SQL 2008 Default DateTime format

Question

Post reply

SQL 2008 Default DateTime format

mirzakhalid82

Old Hand

Points: 306
More actions
June 17, 2010 at 5:15 am

#222210

Hi All
I need to change the SQL 2008 Default DateTime format from
YYYY-mm-dd HH:MM:SS TO dd/mm/yyyy hh:mm:ss
Please help I am stuck now
One other thing I am migrating From SQL 2000 and SQL 2005 to SQL 2008.
Kind Regards

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

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

Stefan_G SSCertifiable Points: 6609 More actions · Answer 1

Exactly what are you trying to do ?

Are you trying to convert from a string to a date, or from a date to a string ?

Is the conversion done inside a stored procedure, or is it done in a client application ?

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

If you are concerned about formatting output, you would be far better off handling all of that through whatever application you're using for the user interface. If you must pre-format in SQL Server, take a look at the CONVERT function in Books Online.

If you are having issues with how SQL Server is interpreting inputted dates, take a look at this article.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

If SQL Server is conveting a string to DATETIME, there are only 2 unambiguous formats that SQL Server will always parse correctly:

'YYYYMMDD HH:MM:SS:mmm', example: '20100617 15:46:59.997'

'YYYY-MM-DDTHH:MM:SS:mmm', example: '2010-06-17T15:46:59.997'

Any other format can have problems, depending on the settings of DATEFORMAT and LANGUAGE.

nikhil0416 SSC Veteran Points: 209 More actions · Answer 4

As far as I know, upgrading the SQL Server does not require changing the default format of dates 🙂 Why do you need to do it?

mirzakhalid82 Old Hand Points: 306 More actions · Answer 5

Hi All

Thank you for you reply. The Whole story is this. I am migrating my online systems from Windows Server 2003 to Win Server 2008 and SQL Server 2000 to Server 2008. Everything was fine but a date foremat wasnt good. Because in SQL 2000 date format was "dd/mm/yyyy" and in 2008 it is "yyyy-mm-dd" and when i was trying to insert the valuse into DB table from existing huge online system it was sending a the conversion error.

BUT EVERY THING IS SORTED NOW. I JUST CHANGE THE LANGUAGE OF LOGIN FROM ENGLISH TO BRITISH ENGLISH.

call.copse SSCoach Points: 17958 More actions · Answer 6

Thanks for this thread. I had a similar issue when I migrated a database to a new 2008 server. A pre-existing login had the wrong language causing the issues above, this thread made me check and realise the problem.

BTW much as I respect you Mr Celko, you are overly harsh above - I am fully cognisant of the correct way to do dates, unfortunately many of us have to deal with non-ideal code we cannot change and that we need to keep working, much against our preferences.

Lempster SSCoach Points: 15651 More actions · Answer 7

call.copse (2/27/2014)
BTW much as I respect you Mr Celko, you are overly harsh above - I am fully cognisant of the correct way to do dates, unfortunately many of us have to deal with non-ideal code we cannot change and that we need to keep working, much against our preferences.

+ 1

You might want to read the ANSI/ISO Standards

..particularly if you have trouble sleeping 😉