SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Weird datetime conversions


Weird datetime conversions

Author
Message
anders-731262
anders-731262
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1033 Visits: 359
Comments posted to this topic are about the item Weird datetime conversions
Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3836 Visits: 5190
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


RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15534 Visits: 9518
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."
Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3836 Visits: 5190
The question looks like this (at least on my PC)...


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




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


Douglas Duncan
Douglas Duncan
SSC Eights!
SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)

Group: General Forum Members
Points: 883 Visits: 325
Atif Sheikh (5/6/2009)
The question looks like this (at least on my PC)...


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




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. Wink
mverma4you
mverma4you
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 226
your question may lead confusion as you are usign third party tool to write the query. your TSQL include code inside the TSQL statement.
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11446 Visits: 12007
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
Jan Van der Eecken
Jan Van der Eecken
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2474 Visits: 6494
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)
anders-731262
anders-731262
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1033 Visits: 359
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.
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11446 Visits: 12007
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... w00t


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
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