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 12»»

Weird datetime conversions Expand / Collapse
Author
Message
Posted Wednesday, May 6, 2009 8:38 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:13 AM
Points: 984, Visits: 350
Comments posted to this topic are about the item Weird datetime conversions
Post #711730
Posted Wednesday, May 6, 2009 9:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:41 AM
Points: 3,241, Visits: 4,999
The given code gives error that is NOT the option in the provided options...

The Error is :

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #711742
Posted Wednesday, May 6, 2009 10:00 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
I couldn't really read this question, It was all just IFCodes.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #711746
Posted Wednesday, May 6, 2009 10:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:41 AM
Points: 3,241, Visits: 4,999
The question looks like this (at least on my PC)...

<SPAN class=codeBlue>SELECT</SPAN> CONVERT(<SPAN class=codeBlue>datetime</SPAN>,-1)
<SPAN class=codeBlue>UNION
SELECT</SPAN> CONVERT(<SPAN class=codeRed><SPAN class=codeBlue>datetime</SPAN></SPAN>,0)+CONVERT(<SPAN class=codeBlue>datetime</SPAN>,-1)
<SPAN class=codeBlue>UNION
SELECT</SPAN> CONVERT(<SPAN class=codeBlue>datetime</SPAN>,-1)-CONVERT(<SPAN class=codeBlue>datetime</SPAN>,0)
<SPAN class=codeBlue>UNION
SELECT</SPAN> CONVERT(<SPAN class=codeBlue>datetime</SPAN>,0)-CONVERT(<SPAN class=codeBlue>datetime</SPAN>,1)
<SPAN class=codeBlue>UNION
SELECT</SPAN> CONVERT(<SPAN class=codeBlue>datetime</SPAN>,0)-1


Am I about to learn learn something new here, or is it a formating issue...?


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #711749
Posted Wednesday, May 6, 2009 10:14 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 14, 2012 6:45 AM
Points: 881, Visits: 324
Atif Sheikh (5/6/2009)
The question looks like this (at least on my PC)...

<SPAN class=codeBlue>SELECT</SPAN> CONVERT(<SPAN class=codeBlue>datetime</SPAN>,-1)
<SPAN class=codeBlue>UNION
SELECT</SPAN> CONVERT(<SPAN class=codeRed><SPAN class=codeBlue>datetime</SPAN></SPAN>,0)+CONVERT(<SPAN class=codeBlue>datetime</SPAN>,-1)
<SPAN class=codeBlue>UNION
SELECT</SPAN> CONVERT(<SPAN class=codeBlue>datetime</SPAN>,-1)-CONVERT(<SPAN class=codeBlue>datetime</SPAN>,0)
<SPAN class=codeBlue>UNION
SELECT</SPAN> CONVERT(<SPAN class=codeBlue>datetime</SPAN>,0)-CONVERT(<SPAN class=codeBlue>datetime</SPAN>,1)
<SPAN class=codeBlue>UNION
SELECT</SPAN> CONVERT(<SPAN class=codeBlue>datetime</SPAN>,0)-1


Am I about to learn learn something new here, or is it a formating issue...?


Hey Atif, it is a formatting issue. All of the <span class=...> text is HTML markup that was not being properly rendered. I had to copy and paste into a text editor and remove the mark up just to figure out what the code was doing. Hopefully someone comes by soon to resolve the issue, or one could technically say that running the code results in an error. ;)
Post #711751
Posted Wednesday, May 6, 2009 11:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 7, 2014 6:48 PM
Points: 459, Visits: 183
your question may lead confusion as you are usign third party tool to write the query. your TSQL include code inside the TSQL statement.
Post #711773
Posted Thursday, May 7, 2009 12:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 5,965, Visits: 8,216
RBarryYoung (5/6/2009)
I couldn't really read this question, It was all just IFCodes.


The code was properly formatted in the daily mail, but not on the site. For those who have lost their mail, or who don't subscribe to it, here is the code as I found it in my mail:

SELECT CONVERT(datetime,-1)
UNION
SELECT CONVERT(datetime,0)+CONVERT(datetime,-1)
UNION
SELECT CONVERT(datetime,-1)-CONVERT(datetime,0)
UNION
SELECT CONVERT(datetime,0)-CONVERT(datetime,1)
UNION
SELECT CONVERT(datetime,0)-1




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #711797
Posted Thursday, May 7, 2009 2:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 4:58 AM
Points: 2,467, Visits: 6,437
Apart from the formatting issue, what does it really mean if one adds two dates together? Yeah, I can see that adding today's date and tomorrow's date as in

SELECT GETDATE()+GETDATE()+1

yields

2118-09-12 20:25:12.580

as off the moment I ran the query, since it's just treated like a floating point addition that then gets converted back to a datetime, but does it really make sense to attempt such a thing in the first place?

Anyhow, I got the answer wrong, but did learn something new since I was under the impression that converting a negative integer to a datetime would cause some kind of conversion error.


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #711829
Posted Thursday, May 7, 2009 2:33 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:13 AM
Points: 984, Visits: 350
mverma4you (5/6/2009)
your question may lead confusion as you are usign third party tool to write the query. your TSQL include code inside the TSQL statement.


Hi,
i cant tell you what went wrong with the formatting. This question was written using the inline editor in the contribution center.

Its too bad, since it more or less ruined the question.
Post #711833
Posted Thursday, May 7, 2009 3:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 5,965, Visits: 8,216
Jan Van der Eecken (5/7/2009)
Apart from the formatting issue, what does it really mean if one adds two dates together?


Nothing. The explanation is in fact misleading, since that is not what is happening here.

If you check the "addiciton" topic in Books Online, you'll see that it mainly describes addition of two numbers, but also mentions the possibility of adding a number (in days) to a date. So this means that there are two alllowed forms of addition:

1. number + number
2. datetime + number

The form used in this QotD: datetime + datetime is not supported - but it can be made into a supported form by first implictly converting the second datetime expressions to a numeric expression.

Note that this is just an explanation and in no way an attempt to condone this behaviour. Even though the datetime + number form of addition is documented, I would never use it in production code as I consider it a gruesome hack. Please, everybody, use DATEADD(day, number, datetime) instead.

Oh, and I also never rely on the current behaviour of implicit conversion between datetimes and numerics. (shudder)

I was under the impression that converting a negative integer to a datetime would cause some kind of conversion error.


If will if you use smalldatetime instead of datetime...



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #711839
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse