error converting string to datetime

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stormogulen - 09 Jul 2008 09:05 GMT

Hi All!

I'm having some problems converting a string to a datetime.

For some reason some dates in my db are stored in varchar-fields. I
have to convert these to datetimes, because I have to make
calculations with them.

This seems to work well and automatic on SQL Server 2005.

But in some countries I get reports back, about errors that occur when
this is done on SQL server 2000.
I know that I can use the CONVERT function, but I cannot seem to
figure out which format the old data that is already in the db have,
and therefore which format code I should use.

Does anybody know what affects the format that SQL server
automatically choses for date formats? I would think that I could
change the regional settings of my local machine, and recreate the
error, but I cannot.

Best regards
Bjarne

Reply to this Message

Uri Dimant - 09 Jul 2008 09:16 GMT

Hi
http://www.karaszi.com/SQLServer/info_datetime.asp

> Hi All!
>
[quoted text clipped - 19 lines]
> Best regards
> Bjarne

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 09 Jul 2008 13:51 GMT

Since you are storing the datetime values in varchar format, it is virtually
impossible to tell what the date values really should be. Imagine you have:

05/06/07 04:32
2008-04-01 06:45
20040431 04:45
06/05/07 04:55

Now you can easily weed out row 3 because it is not a date. But for rows 1
and 4, which one is May 6th 2007, which is June 5th 2007, are are they both
the same? Who knows?

I don't want to sound like Celko, but fix the design. Store datetime values
in datetime columns, and then your problem at least partially disappears.
(You still need to ensure that you use parameterized statements when saving
data, and that your application correctly interprets string literals entered
by users.)

If you need to continue storing them in varchar columns, then fix the
application. Make sure string literals are stored in an unambiguous format
that is not vulnerable to regional settings, locale, dateformat settings,
language, etc. For dates only:

YYYYMMDD

For dates with time:

YYYY-MM-DDTHH:MM:SS.mmm

Yes, that T is meant to be there.

On 7/9/08 4:05 AM, in article
f0aa47dc-9aca-4863-b4af-cd49ee1b9714@34g2000hsh.googlegroups.com,

> Hi All!
>
[quoted text clipped - 19 lines]
> Best regards
> Bjarne

Reply to this Message