Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2008 Default DateTime format Expand / Collapse
Author
Message
Posted Thursday, June 17, 2010 5:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 4, 2010 5:15 PM
Points: 4, Visits: 14
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
Post #938797
Posted Thursday, June 17, 2010 6:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 5, 2015 7:17 AM
Points: 316, Visits: 912
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 ?

Post #938841
Posted Thursday, June 17, 2010 11:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 14, 2014 4:49 PM
Points: 1,093, Visits: 1,175
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
Post #939112
Posted Thursday, June 17, 2010 1:54 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, April 20, 2015 1:28 AM
Points: 3,158, Visits: 11,768
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.




Post #939240
Posted Thursday, June 17, 2010 1:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 12:53 PM
Points: 5, Visits: 8
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?
Post #939245
Posted Friday, June 18, 2010 2:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 4, 2010 5:15 PM
Points: 4, Visits: 14
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.
Post #939423
Posted Thursday, February 27, 2014 5:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 22, 2015 1:50 AM
Points: 2,011, Visits: 1,327
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.
Post #1545796
Posted Thursday, February 27, 2014 5:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 18, 2015 4:51 AM
Points: 2,268, Visits: 1,631
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
Post #1545798
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse