Couln't get result cause invalid date format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Couln't get result cause invalid date format

Author

Topic

abelegreen
Starting Member

2 Posts

Posted - 2010-02-19 : 06:16:16

Hi all!
I have a table with following fields(id,date,description).In my computer,originally my cultureinfo is set up "en-US", so field "date" is formatted like "2/19/2010" (it means February, 19 ,2010).When I execute a query in which have a condition to compare with the "date" filed,I have to set my input date by "en-US", to get the result, and it worked normally.
The fact is when I changed my cultureInfo at my computer to "ru-RU"(russian),with format date is "19.02.2010",so I couldn't get the result from database,the error is invalid date format,though my input date had the same format(ru-RU), and at this time in database the "date" field is also displayed like "19.02.2010"(I saw it in SQL server management studio).When I changed input's format like "2/19/2010", it worked normally again though CultureInfo at my computer was already changed to Russian(ru-RU). I didn't understand why it happened strange like that.
So my question is how to execute query with other format CultureInfo? Thanks so much for any reply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 06:19:04

you dont have to worry about culture or local settings provided you pass date always in universal format ccyymmdd

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-02-19 : 06:19:33

Use the ISO date format '2009-02-19 00:00:00' instead of regional variations.

If you really need to, use CONVERT(varchar(20),<yourdate>,120)

abelegreen
Starting Member

2 Posts

Posted - 2010-02-19 : 06:55:47

Thanks RickD and visakh16 again. I've just done by RickD's way and it worked ok. But still don't understand why SQL doesn't understand local setting date format while it can display normally data by that format :(. It made me confuse.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 07:10:10

quote:
Originally posted by abelegreen

Thanks RickD and visakh16 again. I've just done by RickD's way and it worked ok. But still don't understand why SQL doesn't understand local setting date format while it can display normally data by that format :(. It made me confuse.

its not that it doesnt understand but it rather tries to interpret date as per current settings so if passed date is not in expected format it complains that its invalid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 07:22:09

quote:
Originally posted by RickD

Use the ISO date format '2009-02-19 00:00:00' instead of regional variations.

If you really need to, use CONVERT(varchar(20),<yourdate>,120)

You forgot the time seperator

'2009-02-19T00:00:00'

Sometimes back you had that problem

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 07:24:24

quote:
Originally posted by madhivanan

quote:
Originally posted by RickD

Use the ISO date format '2009-02-19 00:00:00' instead of regional variations.

If you really need to, use CONVERT(varchar(20),<yourdate>,120)

You forgot the time seperator

'2009-02-19T00:00:00'

Sometimes back you had that problem

Madhivanan

Failing to plan is Planning to fail

And I remember Kristen pointing a problem with even hyphens

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-02-19 : 07:29:52

quote:
Originally posted by madhivanan

quote:
Originally posted by RickD

Use the ISO date format '2009-02-19 00:00:00' instead of regional variations.

If you really need to, use CONVERT(varchar(20),<yourdate>,120)

You forgot the time seperator

'2009-02-19T00:00:00'

Sometimes back you had that problem

Madhivanan

Failing to plan is Planning to fail

You've got a better memory than me..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 07:31:13

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by RickD

Use the ISO date format '2009-02-19 00:00:00' instead of regional variations.

If you really need to, use CONVERT(varchar(20),<yourdate>,120)

You forgot the time seperator

'2009-02-19T00:00:00'

Sometimes back you had that problem

Madhivanan

Failing to plan is Planning to fail

And I remember Kristen pointing a problem with even hyphens

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

There wont be problem with hyphens if used with Time seprator T

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 07:31:46

quote:
Originally posted by RickD

quote:
Originally posted by madhivanan

quote:
Originally posted by RickD

Use the ISO date format '2009-02-19 00:00:00' instead of regional variations.

If you really need to, use CONVERT(varchar(20),<yourdate>,120)

You forgot the time seperator

'2009-02-19T00:00:00'

Sometimes back you had that problem

Madhivanan

Failing to plan is Planning to fail

You've got a better memory than me..

Yes it is

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 07:34:17

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by RickD

Use the ISO date format '2009-02-19 00:00:00' instead of regional variations.

If you really need to, use CONVERT(varchar(20),<yourdate>,120)

You forgot the time seperator

'2009-02-19T00:00:00'

Sometimes back you had that problem

Madhivanan

Failing to plan is Planning to fail

And I remember Kristen pointing a problem with even hyphens

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

There wont be problem with hyphens if used with Time seprator T

Madhivanan

Failing to plan is Planning to fail

Yeah..I remember that now
Certainly your memory is great Madhi

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 07:37:29

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by RickD

Use the ISO date format '2009-02-19 00:00:00' instead of regional variations.

If you really need to, use CONVERT(varchar(20),<yourdate>,120)

You forgot the time seperator

'2009-02-19T00:00:00'

Sometimes back you had that problem

Madhivanan

Failing to plan is Planning to fail

And I remember Kristen pointing a problem with even hyphens

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

There wont be problem with hyphens if used with Time seprator T

Madhivanan

Failing to plan is Planning to fail

Yeah..I remember that now
Certainly your memory is great Madhi

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 10:49:41

'20090219'

or

'2009-02-19T00:00:00'

are the only ways to reliably pass a date as a string. (unless you only want to transfer Year or Year+Month only)

Any other format should use an explicit cast - otherwise it may fail if the server settings change / a user logins in with a different language setting ... i.e. there is possible / probably pain downstream!

Of course it would be nice if SQL had an option to warn about this (and other "loose" coding styles) because even though I know about it, I mentioned it here often

, I cannot guarantee 100% that there are no incorrectly formatted dates anywhere in my code

Perhaps its unfortunate that (right from early versions) SQL will have-a-go at implicitly casting any date format that it sees

I wonder if Upgrade Advisor complains about string-date formatting?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources