SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jackal / Date data type / Latest Posts

RE: Date data type

Parag_79 — Fri, 07 May 2010 04:04:17 GMT

I nearly goofed it up. Managed to get correct answer.Explanation was eyeopener, I must say that I need to start more work on SQL 2K8. Its a good question. Regards,Parag

RE: Date data type

Anipaul — Wed, 28 Apr 2010 23:10:33 GMT

Great question....

RE: Date data type

john.arnott — Wed, 28 Apr 2010 11:33:34 GMT

[quote][b]webrunner (4/28/2010)[/b][hr]........ Most other answers include a reference link, and I didn't see one for this question.........................[/quote]To save anyone the trouble of finding where the heck this is, it's documented under the "+" arithmetic add operator:[quote][b]Syntax[/b]expression + expression[b]Arguments[/b]--------------------------------------------------------------------------------expression Is any valid expression of any one of the data types in the numeric category except the bit data type. Cannot be used with date or time data types.[/quote] Note that last sentence -- you can't add with DATE or TIME datatypes.See [b][url]http://msdn.microsoft.com/en-us/library/ms178565(v=SQL.100).aspx[/url][/b]

RE: Date data type

SQLRNNR — Wed, 28 Apr 2010 10:35:40 GMT

Thanks for the question.

RE: Date data type

Trey Staker — Wed, 28 Apr 2010 09:00:30 GMT

I almost missed this one but reread it at the last moment before hitting submit and saw DATE instead of DATETIME. I've always used the DATEADD but have supported a lot of code written by other with code like getdate() + 1.

RE: Date data type

Oleg Netchaev — Wed, 28 Apr 2010 08:50:20 GMT

[quote][b]skjoldtc (4/28/2010)[/b][hr]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.[/quote]The functionality to allow conversion of int to datetime exists in SQL Server 2005/2008 and it is identical. Datetime needs 8 bytes of storage (2 integers). One int (first 4 bytes) is used to store the number of days from the zero datetime ('1900-01-01') and the other to store the number of ticks from midnight. This is why you can add number to an instance of a date.[code="sql"]select cast(40147 as datetime); -- displays 2009-12-02 00:00:00.000select cast(-53690 as datetime); -- displays smallest datetime value '1753-01-01'select cast(getDate() as int); -- displays 40294select getDate() + 1; -- displays tomorrows date same time[/code]The reason the latter works (or why the addition of int to a datetime works) is because [b]1[/b] is first implicitly converted to datetime ([b]'Dec [i]2[/i] 1900'[/b]) and [b]+[/b] operator is legal with datetime values. So to the database engine the statement [code="sql"]select getDate() + 1; [/code]is identical to[code="sql"]select getDate() + cast(1 as datetime); -- 1 as datetime is '1900-01-02'[/code]is identical to[code="sql"]select getDate() + cast('1900-01-02' as datetime);[/code]This is the reason it works (All of the above assumes En-US format yyyy-mm-dd).It looks like with date data type in SQL Server 2008 the situation is different because date needs 3 bytes of storage and therefore it would be stupid for the database team to allow implementation of addition of a 4 byte int to the 3 byte struct. Thus, they don't allow it raising operand clash exception.Oleg

RE: Date data type

Hugo Kornelis — Wed, 28 Apr 2010 08:23:48 GMT

[quote][b] Chad Crawford (4/28/2010)[/b][hr]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.[quote][b]Hugo Kornelis (4/28/2010)[/b][hr]In SQL Server 2008, not terminating statements with a semicolon is deprecated.[/quote]This was even a bigger surprise. I saw that you could do it in 2K8[/quote]You could do it in SQL 2005 and SQL 2000 as well. Probably in 7.0 too, maybe even before that, but my memory sometimes fails me.

RE: Date data type

Lynn Pettis — Wed, 28 Apr 2010 08:06:21 GMT

In SQL Server 2008, just as with the DATE data type, this is also true of datetimeoffset and datetime2. If you have SQL Server 2008 available, try those data types as well.

RE: Date data type

Chad Crawford — Wed, 28 Apr 2010 08:01:04 GMT

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.[quote][b]Hugo Kornelis (4/28/2010)[/b][hr]In SQL Server 2008, not terminating statements with a semicolon is deprecated.[/quote]This was even a bigger surprise. I saw that you could do it in 2K8, but didn't know that [i]not[/i] doing it was deprecated. Wow. One more thing to add to the upgrade list.Thanks,Chad

RE: Date data type

Steve Cullen — Wed, 28 Apr 2010 07:34:18 GMT

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.

RE: Date data type

Paul White — Wed, 28 Apr 2010 07:31:46 GMT

[quote][b]Hugo Kornelis (4/28/2010)[/b][hr]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).[/quote]My sentiments exactly. A good question - but a shame about some of the details.[quote]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).[/quote]I tend to agree (and wish for a proper interval data type).However, it appears that [i]Steve Kass[/i] disagrees (or did at the time):[url]https://connect.microsoft.com/sql/feedback/ViewFeedback.aspx?FeedbackID=290011[/url]

RE: Date data type

webrunner — Wed, 28 Apr 2010 07:31:33 GMT

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

RE: Date data type

Ray K — Wed, 28 Apr 2010 07:03:15 GMT

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!

RE: Date data type

OCTom — Wed, 28 Apr 2010 06:47:59 GMT

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.

RE: Date data type

kaspencer — Wed, 28 Apr 2010 03:53:11 GMT

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

RE: Date data type

Koen Verbeeck — Wed, 28 Apr 2010 03:35:20 GMT

[quote][b]M@N@ (4/28/2010)[/b][hr]Yes, you are right,The 'DATE' datatype is exists on SQL Server 2005/2008 but i told that in below QUERY[b]DECLARE @Today DATESELECT @Today = '01 DEC 2009'SELECT @Today+1SELECT @Today[/b]IF use @Today as DATE datatype we will get error like[b]'Operand type clash: date is incompatible with int'[/b]so if change Datatype DATE to DATETIME, then it will work and we will get Result '2009-12-02 00:00:00.000'Best RegardsM@N@[/quote]Well, thats kind the idea of the question, isn't it? You just explained the answer that was already there.

RE: Date data type

M@N@ — Wed, 28 Apr 2010 03:15:30 GMT

Yes, you are right,The 'DATE' datatype is exists on SQL Server 2005/2008 but i told that in below QUERY[b]DECLARE @Today DATESELECT @Today = '01 DEC 2009'SELECT @Today+1SELECT @Today[/b]IF use @Today as DATE datatype we will get error like[b]'Operand type clash: date is incompatible with int'[/b]so if change Datatype DATE to DATETIME, then it will work and we will get Result '2009-12-02 00:00:00.000'Best RegardsM@N@

RE: Date data type

Niths — Wed, 28 Apr 2010 03:06:15 GMT

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

RE: Date data type

Koen Verbeeck — Wed, 28 Apr 2010 02:59:03 GMT

[quote][b]nithya.s (4/28/2010)[/b][hr]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??[/quote]Here you go:[url]http://msdn.microsoft.com/en-us/library/ms186819.aspx[/url]

RE: Date data type

Niths — Wed, 28 Apr 2010 02:50:22 GMT

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??

RE: Date data type

Koen Verbeeck — Wed, 28 Apr 2010 01:36:45 GMT

[quote][b]M@N@ (4/28/2010)[/b][hr]Hi to all,DECLARE @Today DATEit wii not work but if you change datetype "DATE" to "DATETIME"Then it will work[/quote]Why wouldn't that work? The date datatype exists on SQL Server 2008.

RE: Date data type

Hugo Kornelis — Wed, 28 Apr 2010 01:19:15 GMT

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!

RE: Date data type

M@N@ — Wed, 28 Apr 2010 01:04:02 GMT

Hi to all,1.DECLARE @Today DATEit wii not work but if you change datetype "DATE" to "DATETIME"Then it will workDECLARE @Today DATETIMESELECT @Today = '01 DEC 2009'SELECT @Today+1SELECT @Today2.Directly we can't add integer value to date variable, by using DATEADD function we can add day(s)/year(s) to date variable.

RE: Date data type

Koen Verbeeck — Wed, 28 Apr 2010 00:32:03 GMT

Good question. No points for me but I learned something new today.And also useful, when we will (finally) migrate to 2008.

RE: Date data type

Abrar Ahmad_ — Tue, 27 Apr 2010 23:27:34 GMT

[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]

RE: Date data type

malleswarareddy_m — Tue, 27 Apr 2010 23:25:16 GMT

Iam not using sql server 2008.so I learned one new thing today.

RE: Date data type

Oleg Netchaev — Tue, 27 Apr 2010 20:55:21 GMT

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

Date data type

NicHopper — Tue, 27 Apr 2010 20:45:19 GMT

Comments posted to this topic are about the item [B]Date data type[/B]