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 «««56789»»»

Fun(?) with DATETIME2 Expand / Collapse
Author
Message
Posted Tuesday, February 15, 2011 10:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
Oh, okay. I think I get what the issue is. Thank you for the second link.

I just don't understand why people would use varchar (or any string) to represent dates given the problems that always causes.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1064404
Posted Tuesday, February 15, 2011 12:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:37 PM
Points: 2,706, Visits: 798
I prefer something I can rely on to work for all cases with dates... my favorite is

CONVERT(varchar(8),getdate(),112)='20110215'

because it is easy to remember and there are not special characters and it is always numeric. Not so good if I need a time, but for many processes, (auditing aside) there is no need to check time.


Jamie
Post #1064489
Posted Tuesday, February 15, 2011 1:38 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
Jamie Longstreet-481950 (2/15/2011)
I prefer something I can rely on to work for all cases with dates... my favorite is

CONVERT(varchar(8),getdate(),112)='20110215'

because it is easy to remember and there are not special characters and it is always numeric. Not so good if I need a time, but for many processes, (auditing aside) there is no need to check time.

Looks familiar... I use format 112 a lot, too, but generally with char(8) rather than varchar(8). Is there an advantage to use varchar() that I'm missing? Or is it just one of those "depends" things?
Post #1064541
Posted Tuesday, February 15, 2011 4:29 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:37 PM
Points: 2,706, Visits: 798
No, char(8) is better. Like I said, sometimes I just prefer not to have to think about it.

Jamie
Post #1064628
Posted Wednesday, February 16, 2011 4:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
john.arnott (2/15/2011)
Jamie Longstreet-481950 (2/15/2011)
I prefer something I can rely on to work for all cases with dates... my favorite is

CONVERT(varchar(8),getdate(),112)='20110215'

because it is easy to remember and there are not special characters and it is always numeric. Not so good if I need a time, but for many processes, (auditing aside) there is no need to check time.

Looks familiar... I use format 112 a lot, too, but generally with char(8) rather than varchar(8). Is there an advantage to use varchar() that I'm missing? Or is it just one of those "depends" things?


varchar(8) is not only unnecessary, but it requires extra overhead (all var data types do) to maintain it's flexibility between 1-8 "spaces". (Space is a poor word choice on my part, but I'm too tired to remember the proper word here).

Stick with char(8) since your "dates" will never be less or more. In the long run, it could save you some performance and space problems.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1064827
Posted Wednesday, February 16, 2011 4:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:37 PM
Points: 2,706, Visits: 798
char - characters...

The brain sleeps when it runs out of energy. Often it is smarter than we are.


Jamie
Post #1064830
Posted Wednesday, February 16, 2011 4:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
Jamie Longstreet-481950 (2/16/2011)
The brain sleeps when it runs out of energy. Often it is smarter than we are.


Yeah, and supposedly I just woke up from a good night's sleep. @=)

Actually, characters is not the word I was looking for. There is another, more appropriate SQL Server word that I just can't remember. It's not digits either.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1064840
Posted Wednesday, February 16, 2011 4:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:37 PM
Points: 2,706, Visits: 798
ansi?

Jamie
Post #1064842
Posted Tuesday, March 29, 2011 1:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 18, 2011 12:30 AM
Points: 2, Visits: 10
Plz Tell me new things in sql server 2008 , I want to know it and want to use this sql server for my next project.
Post #1085336
Posted Tuesday, March 29, 2011 7:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 7:18 PM
Points: 7,930, Visits: 9,654
vikram.gharge (3/29/2011)
SQL Server 2008 brought us some new date types including DATETIME2 (which appears better in every way than DATETIME). But what happens if you run the following script (it will only work in SQL Server 2008 or later)?

DECLARE @myDate DATETIME2SET @myDate = '1752-09-09'PRINT @myDate
By Richard Warr

Answer: It runs perfectly but you would expect it to fail.

Explanation: The DATETIME2 type removes the "pre-1753" restriction of DATETIME. But it is unable to cater for the fact that there was no such date as 9th September 1752. Were it perfect we'd see:

"Msg 241, Level 16, State 1, Line 3 Conversion failed when converting date and/or time from character string."
Because of the move to the Gregorian Calendar, Britain and its possessions (including the USA - this WAS 1752) lost 11 days in September so there was nothing between the 2nd and the 13th.
There's some fun to be had with 1751 as well but we'll leave that for another holiday.



This is just nonsense - the DATETIME2 datatype represents dates in the Gregorian calendar, not dates that have been used in the national calendar of Great Britain and its colonies; the date 9th September 1752 was not used in that national calendar, but there certainly is such a date in the Gregorian calendar. So DATETIME2 is already perfect, and preventing it from representing that date would render it imperfect, not improve it.


Tom
Post #1085520
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse