Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Abhijit » Date Puzzle
Add to briefcase
48 posts, Page 1 of 5
12345»»»

Date Puzzle Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
abmore
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: Thursday, May 16, 2013 8:06 AM
Points: 1,134, Visits: 818
Comments posted to this topic are about the item Date Puzzle
Post #1011249
sharath.chalamgari
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: Monday, September 17, 2012 7:30 AM
Points: 1,038, Visits: 679
Simple but most of the people forget when they use it. Thanks any way
Post #1011250
Hardy21
Posted Tuesday, October 26, 2010 10:44 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:11 AM
Points: 877, Visits: 1,159
Question is simple but nice one.

Thanks
Post #1011256
henrik staun poulsen
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: Today @ 1:49 AM
Points: 1,131, Visits: 855
'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
Hardy21
Posted Wednesday, October 27, 2010 12:29 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:11 AM
Points: 877, Visits: 1,159
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
Koen Verbeeck
Posted Wednesday, October 27, 2010 1:40 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 9,372, Visits: 6,470
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
Hugo Kornelis
Posted Wednesday, October 27, 2010 1:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:06 AM
Points: 5,235, Visits: 7,041
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
archie flockhart
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: Friday, May 17, 2013 5:13 AM
Points: 1,123, Visits: 986
I might have had to think twice if the possible answers had been April 30th, April 31st and May 1st.
Post #1011317
Hugo Kornelis
Posted Wednesday, October 27, 2010 1:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:06 AM
Points: 5,235, Visits: 7,041
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
hodgy
Posted Wednesday, October 27, 2010 2:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:55 AM
Points: 1,865, Visits: 556
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
48 posts, Page 1 of 5
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.