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

Date data type Expand / Collapse
Author
Message
Posted Wednesday, April 28, 2010 3:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 08, 2010 7:47 AM
Points: 26, Visits: 42
Cool !!!! Thanks a lot !!

------------------------
~Niths~
Hard Work never Fails
Post #911712
Posted Wednesday, April 28, 2010 3:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 10, 2010 5:13 AM
Points: 40, Visits: 18
Yes, you are right,The 'DATE' datatype is exists on SQL Server 2005/2008 but i told that in below QUERY

DECLARE @Today DATE
SELECT @Today = '01 DEC 2009'
SELECT @Today+1
SELECT @Today


IF use @Today as DATE datatype we will get error like
'Operand type clash: date is incompatible with int'

so if change Datatype DATE to DATETIME, then it will work and we will get Result
'2009-12-02 00:00:00.000'

Best Regards
M@N@


Thanks & Regards
M@N@[size="7"][/size]
Post #911716
Posted Wednesday, April 28, 2010 3:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 12,239, Visits: 9,212
M@N@ (4/28/2010)
Yes, you are right,The 'DATE' datatype is exists on SQL Server 2005/2008 but i told that in below QUERY

DECLARE @Today DATE
SELECT @Today = '01 DEC 2009'
SELECT @Today+1
SELECT @Today


IF use @Today as DATE datatype we will get error like
'Operand type clash: date is incompatible with int'

so if change Datatype DATE to DATETIME, then it will work and we will get Result
'2009-12-02 00:00:00.000'

Best Regards
M@N@


Well, thats kind the idea of the question, isn't it? You just explained the answer that was already there.




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 #911732
Posted Wednesday, April 28, 2010 3:53 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:12 AM
Points: 1,641, Visits: 423
That question revealed quite a significant difference between SQL Server 2005 and 2008.

With SQL Server 2005, the datatype DATE is not valid, and so if the datatype of the variable is amended to DATETIME, the entire code set succeeds, and gives the answer 2nd December 2009, in whatever format. The datepart of the value to be added defaults to DAY.

Yet, SQL 2008 is more rigorous in it's insistence on a correct datatype and datepart for the added day, when the datatype of the variable is DATE.

Something to watch out for there, eh!

Kenneth Spencer
Post #911749
Posted Wednesday, April 28, 2010 6:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 2,417, Visits: 3,461
Good question. The difference between DATE and DATETIME in SQL Server 2008 is important. Thanks.

The answer 40147 looked familiar to me. It is what I have known to be called a "hundred-year" format date. I have seen it used on the IBM i. 40147 is that format equivalent to 2009-12-01. It's not really important. I just found it interesting.
Post #911872
Posted Wednesday, April 28, 2010 7:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:12 PM
Points: 2,132, Visits: 3,399
Wow . . . I was going to answer (1), until I tried running the query, and was surprised by what came back.

I was skeptical when I first read the question, but it turned out to be better than I expected. Good question!
Post #911900
Posted Wednesday, April 28, 2010 7:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:34 PM
Points: 2,290, Visits: 2,548
Great question. I think the answer choices were suitably plausible, so it made it hard for me to guess. I happened to guess right. Seriously, though, this is good information to know, and the question illustrated it well.

I have only one suggestion. Most other answers include a reference link, and I didn't see one for this question. It's easy enough to look up in Books Online, but if you post future questions, including the link will allow people to click right over to it.

Thanks!
webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #911936
Posted Wednesday, April 28, 2010 7:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
Hugo Kornelis (4/28/2010)
What I dislike about the question is:
* The date format. Not all locale settings recognise "Dec" as december. (Try adding "SET LANGUAGE German;" as the first line - at least on SQL Server 2005 and with the data type changed to datetime, that results in a conversion error)
* The missing semicolons. In SQL Server 2008, not terminating statements with a semicolon is deprecated.
* There were two SELECT statements, so the incorrect answer options should have included two results sets. (I guess the author intended the first SELECT to read "SELECT @Today = @Today + 1;" - yet another reason to prefer SET over SELECT).

My sentiments exactly. A good question - but a shame about some of the details.

For me (to read carefully and think instead of just shooting from the hip), and for many others (that adding an integer to a date is not supported - just as it should never have been supported for datetime).

I tend to agree (and wish for a proper interval data type).

However, it appears that Steve Kass disagrees (or did at the time):
https://connect.microsoft.com/sql/feedback/ViewFeedback.aspx?FeedbackID=290011




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #911937
Posted Wednesday, April 28, 2010 7:34 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
Good question. I've learned the hard way not to assume I know the answer. I would not have expected the error in this case, and something else new about date data types.

Converting oxygen into carbon dioxide, since 1955.

Post #911938
Posted Wednesday, April 28, 2010 8:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 2,602, Visits: 17,851
This was quite a surprise to me. I would have expected the Date and Datetime to have worked the same way, but I guess that's why I come back here every day.

Hugo Kornelis (4/28/2010)
In SQL Server 2008, not terminating statements with a semicolon is deprecated.

This was even a bigger surprise. I saw that you could do it in 2K8, but didn't know that not doing it was deprecated. Wow. One more thing to add to the upgrade list.

Thanks,
Chad
Post #911973
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse