Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date data type


Date data type

Author
Message
Niths
Niths
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

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

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

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

Koen Verbeeck
Koen Verbeeck
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: 16366 Visits: 13199
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
kaspencer
kaspencer
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2384 Visits: 810
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
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: 2813 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
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2598 Visits: 4554
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
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3027 Visits: 3745
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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"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
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2436 Visits: 18645
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