SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date data type


Date data type

Author
Message
Niths
Niths
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 42
Cool !!!! Thanks a lot !! :-):-D

------------------------
~Niths~
Hard Work never Fails :-)
M@N@
M@N@
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 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]

Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: General Forum Members
Points: 146764 Visits: 13350
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
kaspencer
kaspencer
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3142 Visits: 904
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

You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer
OCTom
OCTom
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6007 Visits: 4152
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.
Ray K
Ray K
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13779 Visits: 4959
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!

+--------------------------------------------------------------------------------------+
‌Check out my blog at https://pianorayk.wordpress.com/
webrunner
webrunner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16890 Visits: 4141
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

-------------------
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
Paul White
Paul White
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80294 Visits: 11400
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Steve Cullen
Steve Cullen
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2775 Visits: 1226
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.


Chad Crawford
 Chad Crawford
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7299 Visits: 18732
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search