CONVERT datetime

[le1234]

help!

I have a current field that is stored as a varchar(500), users enter dates into this field. I need to convert the field to an english date but I am having problems, any ideas would be greatly appreciated....
values when selected in query analyser are shown as
2006/07/02 15:20:00

if I try and run the following convert statement
convert(smalldatetime, Value, 103) Value2
i get an error
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

i tried another conversion of
convert(SMALLDATETIME,SUBSTRING(VALUE,9,2) + SUBSTRING (VALUE,5,4)+ SUBSTRING (VALUE,1,4)+SUBSTRING (VALUE,11,9),103) Value2
but i get the following results
2006-07-02 15:20:00

so i still cant use this in my where statement when tryin to put in english date formats

any ideas????

 

[gmmastros]

Please take a look at this FAQ: faq183-6419

If you have any followup questions after reading the faq's, feel free to post them here.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[le1234]

thanks for that information.
however, I am still confused as to why convert(smalldatetime, Value, 103) Value2 doesnt run, i have used this many times before.

 

[gmmastros]

There are a couple reasons (that I can think of).

1. SmallDateTime has a limited range of values and you have a seemingly valid date outside of that value.

2. You may be having problems with the date format. If users enter the date in Month-Day-Year format, you could have dates like 11/27/2006 and SQL could be 'trying' to interpret that as 11th day of 27th month, which is invalid.

For example, both dates appear to be valid. Unfortunately, each example gives the same error message that you are getting.

Select Convert(smallDateTime, '1/1/2100')

set DateFormat DMY
Select Convert(SmallDateTime, '11/27/2006')

The first one is caused by an out of valid range problem with the date. The second is because the DateFormat is specified as Day-Month-Year, but the date is given in Month-Day-Year format.

Please take a look at this thread: thread183-1240616

Basically what I'm saying is that you have bad data. The original faq's that I pointed to should be helpful in finding the bad data. Hint: use the fnIsSmallDateTime to help find the bad data.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom