Changing default date for database

Question

Post reply

Changing default date for database

Brendan Collins

SSC Eights!

Points: 822
More actions
June 27, 2005 at 10:14 am

#164992

Hi
I have a web application which sends a date value to my SQL Server database in the form of DD/MM/YYYY. Unfortunately my SQL Server database won't allow this and produces an error message on the page. I have been trying to code it so that it sends it in the format MM/DD/YYYY, but so far without success.
I was wondering is it possible to change the default setting for the date on my database so that it will accept DD/MM/YYYY.
Any help is greatly appreciated.
Thanks
Brendan
P.S.
Was wondering if the individual database could have its dates setting changed and not the SQL Server setting for all databases held.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

Can you send the data in the format 'yyyy-mm-dd'? This format should work regardless of American/English date worries.

Regards

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Brendan Collins SSC Eights! Points: 822 More actions · Answer 2

Thanks for the quick reply. Will try to code your solution, just thought it might be easier to change a setting on the database.

Sending dates from web apps to databases seems to take up most of my coding time!

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 3

SET DATEFORMAT is what you're looking for--read the (sparse) details in BOL. This lets you change the date format "used" by the current connection. There's probably a server-wide date format setting somewhere, but I'd recommend leaving that kind of setting to the "out of the box" default values.

What you want to do is have your front-end application use a "date" value (datatype), rather than use a string value. (Keeps the user from entering Feb 31, 2005, or 13/13/05.) Whether this is easy or hard depends on the front-end language you're using.

My number one piece of SQL Server character-to-date translation adivise: use "MMM DD YYYY hh:mm:ss.sss" format (example: Jun 26 2005 13:36.30.333 -- you can safely drop the time stuff.) SQL can always translate this into a datetime value. Probably not too useful for your application, alas.

Philip

Simon Martin SSC-Addicted Points: 418 More actions · Answer 4

Can you change the default language to British English, in Server Properties?

Sharing knowledge saves valuable time!
Simon Martin

Brendan Collins SSC Eights! Points: 822 More actions · Answer 5

Hi

Thanks again for all your help. Have taken Philips advice and coded so that the date format sent to the database is of the format MMM DD YYYY. All seems to be working.

Cheers.

Brendan

Hans Lindgren SSChampion Points: 10494 More actions · Answer 6

Just for information:

SET DATEFORMAT YDM

SELECT CAST( '2005-05-07' AS DATETIME)

SET DATEFORMAT YMD

SELECT CAST( '2005-05-07' AS DATETIME)

Produces WRONG result. I.e you can't trust the format 'YYYY-MM-DD'.

Though going with the ISO 8601 produces the right result:

SET DATEFORMAT YDM

SELECT CAST( '2005-05-07T00:00:00' AS DATETIME)

SET DATEFORMAT YMD

SELECT CAST( '2005-05-07T00:00:00' AS DATETIME)

Phil Parkin SSC Guru Points: 245813 More actions · Answer 7

Yup, I was talking twaddle once again.

Best go home and swat up

Phil

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.