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, November 26, 2013 8:40 AM
Points: 316, Visits: 908
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: Wednesday, December 4, 2013 1:49 PM
Points: 1,104, Visits: 1,174
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:18 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
You might want to read the ANSI/ISO Standards; the ISO-8601 date **display** format "yyyy-mm-dd HH:MM:ss.sssss" is the only one allowed in SQL.

It is also one format that cannot be misread. Look at UK versus US traditional date formats.

You also might want to learn how a tiered architecture works. ALL display formatting is done in the front end and NEVER done in the database.

You are a newbie who needs to do more studying.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #939203
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: Yesterday @ 4:09 PM
Points: 3,121, Visits: 11,395
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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:01 AM
Points: 1,618, Visits: 1,064
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: Today @ 4:50 AM
Points: 2,036, Visits: 1,374
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