Testing a date

Bill Crawley · November 27th, 2003, 12:19 PM

Hi All,

in SQLServer 2000 as part of a Select statement I have:

(CONVERT(char(11), dbo.Submission.GatewayTimestamp, 103) > '13/05/2001')

in my Where clause.

The GatewayTimestamp column is of type datetime.

Why do I have returned records where the date is '24/04/2001' as well as Records > than my date specified?

Imar · November 27th, 2003, 01:08 PM

Your code will convert the date to a string. The string will look like this for today:

'27/11/2003'

As you can see, 27/ is larger than 13/ so you WHERE clause is matched and the record selected.

You may want to try 111 or 112 instead of 103. This will convert the data in a Japanese or ISO format. Both these formats have the Years first, followed by the months and days. (I wish the whole world would stich to that notation). This way, 2001/11/26 will be smaller than 2003/11/13

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Bill Crawley · November 27th, 2003, 01:13 PM

Imar, You missed my point. I'm looking for dates greater than the 13/05/2001, but also get records returned that are less than that date. THe converse is also true (i.e. if I change '>' to '<'....looking for records less than, then it also returns records greater than). Only when I do '=' do I have returned the correct records.

chrislepingwell · November 27th, 2003, 01:20 PM

Actually, I think you missed Imar's point, i.e. that you are comparing text strings, not dates. Therefore, all your '<' and '>' comparisions are being done on the basis of alphanumeric ordering, not any variety of magnitude. The reason that it works when you use '=' is that you are looking for a string exactly equal to your criterion date. If you used '13 fred' as the criterion, you would get similar results to what you are getting now :)

HTH

Chris

There are two secrets to success in this world:
1. Never tell everything you know

Bill Crawley · November 27th, 2003, 01:28 PM

I have also tried:

(CONVERT(datetime, CAST(CONVERT(datetime, dbo.Submission.GatewayTimestamp, 103) AS varChar(11)), 103) > '13/05/2001')

But this just gives a date overflow error.

What sysntax should I be using, could someone give me an example please.

chrislepingwell · November 27th, 2003, 01:45 PM

Just out of interest, what happens if you just use

Code:

dbo.Submission.GatewayTimestamp > '13/05/2001'

Generally, I am able to get implicit conversions, without resorting to cast or convert. And Imar's point about using ISO date format is still a good idea.

There are two secrets to success in this world:
1. Never tell everything you know

Bill Crawley · November 27th, 2003, 01:51 PM

It doesn't work.

chrislepingwell · November 27th, 2003, 01:54 PM

OK, change your date to '20010513' (ISO format) and see what happens.

There are two secrets to success in this world:
1. Never tell everything you know

Bill Crawley · November 27th, 2003, 02:01 PM

it's given me the result I would expect....Worked

chrislepingwell · November 27th, 2003, 02:08 PM

Cool. And now we've got that sorted, we can tell you the answer you wanted in the first place

(103 being UK format dates)

Code:

dbo.Submission.GatewayTimestamp > convert(datetime,'13/05/2001',103)

Your real problem was that you were approaching things from the wrong end: why convert each the date in your datetime field to characters, when you can convert your criterion to a datetime once? If you'd come from that end, you would probably have found the answer to the convert puzzle yourself.:D

Chris

There are two secrets to success in this world:
1. Never tell everything you know