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

Cast as datetime question Expand / Collapse
Author
Message
Posted Tuesday, November 17, 2009 9:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 2:33 PM
Points: 64, Visits: 55
Comments posted to this topic are about the item Cast as datetime question
Post #820551
Posted Wednesday, November 18, 2009 3:48 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 11, 2013 12:41 PM
Points: 621, Visits: 297
That was tricky one.

I didn't knew that when a number is casted to DATETIME, that number is added '1/1/1900' and returns the resulting date.




Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
Post #820688
Posted Wednesday, November 18, 2009 5:34 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 5, 2011 8:18 AM
Points: 582, Visits: 359
Nice question... demonstrates how the subtraction operation implicitly casts the result as an integer data type thus avoiding the error message that '435365' on its own would have caused.
Post #820732
Posted Wednesday, November 18, 2009 5:35 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: Yesterday @ 7:45 AM
Points: 849, Visits: 852
A neat question, but since I didn't see a reason for any of the implicit conversions not to work, I assumed the one datetime value on the list had to be the correct answer. If there was another datetime option I probably would have had to guess.

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #820734
Posted Wednesday, November 18, 2009 6:53 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 6:16 AM
Points: 628, Visits: 1,607
This doesn't work in SQL Server 2005 TSQL , end up with an a Msg 1305...

Post #820786
Posted Wednesday, November 18, 2009 7:23 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: Yesterday @ 7:45 AM
Points: 849, Visits: 852
chrisn-585491 (11/18/2009)
This doesn't work in SQL Server 2005 TSQL , end up with an a Msg 1305...


Works perfectly for me in 2005.


-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #820821
Posted Wednesday, November 18, 2009 7:24 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 5, 2011 8:18 AM
Points: 582, Visits: 359
chrisn-585491 (11/18/2009)
This doesn't work in SQL Server 2005 TSQL , end up with an a Msg 1305...



worked for me in 2005
Post #820824
Posted Wednesday, November 18, 2009 7:36 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 6:16 AM
Points: 628, Visits: 1,607

Restarted Management Studio and the results work now as expected...

Post #820837
Posted Wednesday, November 18, 2009 9:19 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:42 PM
Points: 711, Visits: 677
Interesting, I got this wrong because in Excel 434115 = July 24th 3088, not the 26th. Why there is a difference of 2 days over a 1100+ year gap is beyond me. Sum of leap minutes?
Post #820933
Posted Wednesday, November 18, 2009 9:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 16, 2011 3:52 AM
Points: 288, Visits: 105
Here is how i got it!!!

select dateadd("d",'435365'-1250, cast (0 as datetime))


Hiren


Hiren Shah

KISS (Keep It Simple Stupid)
Post #820943
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse