February 15, 2011 at 10:14 am
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.
February 15, 2011 at 12:06 pm
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
February 15, 2011 at 1:38 pm
Jamie Longstreet-481950 (2/15/2011)
I prefer something I can rely on to work for all cases with dates... my favorite isCONVERT(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?
February 15, 2011 at 4:29 pm
No, char(8) is better. Like I said, sometimes I just prefer not to have to think about it.
Jamie
February 16, 2011 at 4:24 am
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 isCONVERT(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.
February 16, 2011 at 4:27 am
char - characters...
The brain sleeps when it runs out of energy. Often it is smarter than we are.
Jamie
February 16, 2011 at 4:37 am
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.
February 16, 2011 at 4:39 am
ansi?
Jamie
March 29, 2011 at 1:06 am
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.
March 29, 2011 at 7:40 am
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
March 29, 2011 at 9:28 am
vikram.gharge (3/29/2011)
SQL Server 2008 brought us some new date types including DATETIME2 .............There's some fun to be had with 1751 as well but we'll leave that for another holiday.
Vikram,
What's your point in copy/pasting the QOD? Did you mean to comment and forgot?
July 15, 2011 at 6:23 am
Poor question; how do you know what my expectations were?
August 10, 2011 at 5:40 am
I expected it to work and it did ( as it should in most countries except in brittain ) 🙁
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
August 10, 2011 at 9:42 am
hakan.winther (8/10/2011)
I expected it to work and it did ( as it should in most countries except in brittain ) 🙁
Not in most countries except Britain (note single t), in all countries including Britain. Datetime2 uses the Gregorian calendar, not any other calendar, so it is not a locale dependent type.
Tom
August 10, 2011 at 10:06 am
Tom.Thomson (8/10/2011)
. . .Not in most countries except Britain (note single t), in all countries including Britain. Datetime2 uses the Gregorian calendar, not any other calendar, so it is not a locale dependent type.
Hmm... maybe it is not quite Gregorian. If you run this, --
DECLARE @type2 DATETIME2(7) = '1582-10-10';
PRINT DATEPART( dw, @type2 );
you will get 1, i.e., Monday, but that is wrong because there was no October 10 in 1582.
Viewing 15 posts - 61 through 75 (of 81 total)
You must be logged in to reply to this topic. Login to reply