Date data type

Question

Date data type

NicHopper

SSCrazy Eights

Points: 9090
More actions
April 27, 2010 at 8:45 pm

#234307

Comments posted to this topic are about the item Date data type
Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 1

Good question, thank you. I answered correctly because I already tried it some time ago when I first learned about the new data type just to check whether behavior of date is the same with the one of datetime.

Oleg

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 2

Iam not using sql server 2008.

so I learned one new thing today.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Abrar Ahmad_ SSCarpal Tunnel Points: 4244 More actions · Answer 3

[font="Verdana"]Goodish, at least one of the requirements for QOD is fulfilled in this question statement, i.e to include the Version # of SQL.

Although it was obligatory for this question but at least i appreciate this effort and will request other contributors to make question statements clear of ambiguities specially the Version Conflicts.

Thanks !!!

[/font]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Good question. No points for me but I learned something new today.

And also useful, when we will (finally) migrate to 2008.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

M@N@ Valued Member Points: 52 More actions · Answer 5

Hi to all,

1.

DECLARE @Today DATE

it wii not work but if you change datetype "DATE" to "DATETIME"

Then it will work

DECLARE @Today DATETIME

SELECT @Today = '01 DEC 2009'

SELECT @Today+1

SELECT @Today

2.

Directly we can't add integer value to date variable, by using DATEADD function we can add day(s)/year(s) to date variable.

[font="Verdana"]Thanks & Regards
M@N@[/font]

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

Mostly a good question. I got it wrong, because I was too hasty - I overlooked that the data type was DATE, not DATETIME (probably because I still work with SQL2005 on a daily basis). I do know that adding an integer to DATE is not supported.

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).

But the lesson learned is valuable. 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).

Thanks for the question!

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

M@N@ (4/28/2010)
Hi to all,
DECLARE @Today DATE
it wii not work but if you change datetype "DATE" to "DATETIME"
Then it will work

Why wouldn't that work? The date datatype exists on SQL Server 2008.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Niths Old Hand Points: 350 More actions · Answer 8

Gud Quest !! 🙂 I have learnt about casting while trying to solve the error in the Quest... and by the way, can anyone give me the syntax for using 'DATEADD' function??

------------------------
~Niths~
Hard Work never Fails 🙂

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

nithya.s (4/28/2010)
Gud Quest !! 🙂 I have learnt about casting while trying to solve the error in the Quest... and by the way, can anyone give me the syntax for using 'DATEADD' function??

Here you go:

http://msdn.microsoft.com/en-us/library/ms186819.aspx

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Niths Old Hand Points: 350 More actions · Answer 10

Cool !!!! Thanks a lot !! :-):-D

------------------------
~Niths~
Hard Work never Fails 🙂

M@N@ Valued Member Points: 52 More actions · Answer 11

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@

[font="Verdana"]Thanks & Regards
M@N@[/font]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 12

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

kaspencer SSCarpal Tunnel Points: 4880 More actions · Answer 13

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 SSChampion Points: 11785 More actions · Answer 14

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.