Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Anders Hellner » Weird datetime conversions
Add to briefcase
18 posts, Page 1 of 2
12»»

Weird datetime conversions Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
anders-731262
Posted Wednesday, May 06, 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: Thursday, March 28, 2013 7:38 AM
Points: 894, Visits: 317
Comments posted to this topic are about the item Weird datetime conversions
Post #711730
Atif-ullah Sheikh
Posted Wednesday, May 06, 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: Thursday, March 14, 2013 4:15 AM
Points: 3,240, Visits: 4,960
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
RBarryYoung
Posted Wednesday, May 06, 2009 10:00 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
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
Atif-ullah Sheikh
Posted Wednesday, May 06, 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: Thursday, March 14, 2013 4:15 AM
Points: 3,240, Visits: 4,960
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
Douglas Duncan
Posted Wednesday, May 06, 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
mverma4you
Posted Wednesday, May 06, 2009 11:57 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, November 09, 2012 8:46 AM
Points: 459, Visits: 180
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
Hugo Kornelis
Posted Thursday, May 07, 2009 12:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 5,244, Visits: 7,062
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
Jan Van der Eecken
Posted Thursday, May 07, 2009 2:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:12 AM
Points: 2,269, Visits: 5,960
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.

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

The function of good software is to make the complex appear to be simple. (Grady Booch)
Post #711829
anders-731262
Posted Thursday, May 07, 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: Thursday, March 28, 2013 7:38 AM
Points: 894, Visits: 317
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
Hugo Kornelis
Posted Thursday, May 07, 2009 3:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 5,244, Visits: 7,062
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
18 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.