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 12345»»»

Date Puzzle Expand / Collapse
Author
Message
Posted Tuesday, October 26, 2010 10:14 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 1,154, Visits: 866
Comments posted to this topic are about the item Date Puzzle
Post #1011249
Posted Tuesday, October 26, 2010 10:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:39 AM
Points: 1,194, Visits: 787
Simple but most of the people forget when they use it. Thanks any way
Post #1011250
Posted Tuesday, October 26, 2010 10:44 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 1,130, Visits: 1,390
Question is simple but nice one.

Thanks
Post #1011256
Posted Wednesday, October 27, 2010 12:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:13 AM
Points: 1,385, Visits: 998
'31-Jan-2010'
It that the correct (international) way to hard-code dates?

I think I would have used '2010-01-31' or is it '20100131' to ensure that it will work around the globe?



Post #1011287
Posted Wednesday, October 27, 2010 12:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 1,130, Visits: 1,390
henrik staun poulsen (10/27/2010)
'31-Jan-2010'
It that the correct (international) way to hard-code dates?

I think I would have used '2010-01-31' or is it '20100131' to ensure that it will work around the globe?


date is displayed as per your database/server setting.
you need to convert the string into DATE format if you want only DATE portion (applicable for SQL 2k8 only) or DATETIME format if you want date & time both like:
SELECT CONVERT(DATE, '10/27/2010')
if you want to set variable then SELECT @dateValue = CONVERT(DATE, '10/27/2010')


Thanks
Post #1011294
Posted Wednesday, October 27, 2010 1:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
This one was too easy, but thanks for the question anyway.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1011312
Posted Wednesday, October 27, 2010 1:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:48 PM
Points: 6,021, Visits: 8,290
henrik staun poulsen (10/27/2010)
'31-Jan-2010'
It that the correct (international) way to hard-code dates?

I think I would have used '2010-01-31' or is it '20100131' to ensure that it will work around the globe?

Hi Henrik,

A good point. Using '31-Jan-2010' in T-SQL to represent a date is not safe. If the server is set to a language other than English, you might get an error. For instance:
SET LANGUAGE French;
SELECT DATEADD(MONTH, 3, '31-Jan-2010');

Le paramètre de langue est passé à Français.

-----------------------
Msg 241, Level 16, State 1, Line 2
Échec de la conversion d'une valeur datetime à partir d'une chaîne de caractères.

(The text of these messages roughly translate to "language succesfully set to french" / "error converting character value to datetime")

For the datetime data type, The only formats that are guaranteed to work correctly in all circumstances are:
* yyyymmdd - for dates without time portion. No dashes. dots, slashes or other punctuation. The resulting datetime value will have its time portion set to midnight.
* yyyy-mm-ddThh:mm:ss - for dates with a time portion. Dashes between the date components, colons between the time components, and an uppercase T to seperate date from time.
* yyyy-mm-ddThh:mm:ss.mmm - as above, but with microseconds added. These will be rounded to the nearest 1/300 of a second.

For the new date data type, I think (too little hands-on SQL 2008 experience to be sure) that you can choose between these two formats:
* yyyymmdd - same as for datetime values, use when compatibility among date/time types is required
* yyyy-mm-dd - as presribed by ISO standards, but unfortunately not guaranteed for datetime; the new date data type finally allowed the SQL Server team to fix this (though unfortunately not for the old types - for backwards compatibility reasons, I guess)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1011316
Posted Wednesday, October 27, 2010 1:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 2:52 AM
Points: 1,153, Visits: 1,047
I might have had to think twice if the possible answers had been April 30th, April 31st and May 1st.
Post #1011317
Posted Wednesday, October 27, 2010 1:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:48 PM
Points: 6,021, Visits: 8,290
Hardy21 (10/27/2010)
henrik staun poulsen (10/27/2010)
'31-Jan-2010'
It that the correct (international) way to hard-code dates?

I think I would have used '2010-01-31' or is it '20100131' to ensure that it will work around the globe?


date is displayed as per your database/server setting.
you need to convert the string into DATE format if you want only DATE portion (applicable for SQL 2k8 only) or DATETIME format if you want date & time both like:
SELECT CONVERT(DATE, '10/27/2010')
if you want to set variable then SELECT @dateValue = CONVERT(DATE, '10/27/2010')

As far as I know (but again, my SQL 2008 experience is limited), this will fail if you have a server language (or a SET LANGUAGE) that does not use the mm/dd/yyyy format. (Which means, almost everywhere except the US).

For instance (substituting datetime for date, for I run SQL 2005):
SET LANGUAGE British;
SELECT CONVERT(DATETIME, '10/27/2010')

Changed language setting to British.

-----------------------
Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1011318
Posted Wednesday, October 27, 2010 2:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 4:39 AM
Points: 1,865, Visits: 593
archie flockhart (10/27/2010)
I might have had to think twice if the possible answers had been April 30th, April 31st and May 1st.


My thoughts exactly


Life: it twists and turns like a twisty turny thing
Post #1011327
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse