Another DateTime Problem - YYYY/DD/MM

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

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Another DateTime Problem - YYYY/DD/MM

Author

Topic

bassmo
Starting Member

5 Posts

Posted - 2008-08-04 : 08:23:40

Hi,
I am pulling my hair out here. I am using SQL 2005 express and SQL server management studio (also have old SQL SERVER 2000 running). I am also in British language format.

However, and this is the most bonkers thing I have seen - I am unable to insert dates in YYYY/MM/DD - in 2005 express it will accept dates in YYYY/DD/MM - who on earth uses that ?

If inserting 2008/4/14, I get that annoying message "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value".

Is there anywhere I can check/change the expected format for dates for 2005 express??? (Problem doesnt happen on SQL2000 on the same machine)

Thanks in advance

-there aren't wrong notes, just wrong timings-

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 08:35:23

Always use DATETIME datatype to store dates. Express date values in YYYYMMDD format

Madhivanan

Failing to plan is Planning to fail

bassmo
Starting Member

5 Posts

Posted - 2008-08-04 : 08:38:35

it is datetime format.

-there aren't wrong notes, just wrong timings-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-04 : 08:43:06

what madhivanan mean is when inserting, specify the date as '20080414' and not '2008/04/14'

KH
[spoiler]Time is always against us[/spoiler]

bassmo
Starting Member

5 Posts

Posted - 2008-08-04 : 09:07:28

hmm, yes that would be ideal, although I am using a system where the output is fixed as YYYY/MM/DD. This works fine on the databases on the SQL2000 server but not on the express 2005.

-there aren't wrong notes, just wrong timings-

bassmo
Starting Member

5 Posts

Posted - 2008-08-04 : 09:09:45

(It does work if i do a manual insert YYYYMMDD, pity about the / /)

-there aren't wrong notes, just wrong timings-

bassmo
Starting Member

5 Posts

Posted - 2008-08-04 : 10:14:25

Right, i have it fixed - i had to remove and recreate my DSN with British after resetting my user and restarting SQL Express. Whew.

-there aren't wrong notes, just wrong timings-

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-04 : 10:25:07

Where and how are you doing these date inserts?

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 11:03:32

quote:
Originally posted by jsmith8858

Where and how are you doing these date inserts?

- Jeff
http://weblogs.sqlteam.com/JeffS

Many people are interested on WHAT they want to do rather than WHY they want to do

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108022

Many questions like the above for the last two weeks

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources