CAST DateTime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CAST DateTime

Author

Topic

MikeHasNoSQL
Starting Member

3 Posts

Posted - 2014-07-31 : 07:43:53

Hello,

I'm new to SQL (and new to the entire IT world). I'm in the process of trying to learn for myself.

I'm reviewing the CAST function using Microsoft SQL server. I generally understand the functionality, but my confusion lies in the results of a particular script which casts a DateTime value to a fixed-point number.
It is as follows:

DECLARE @From DATETIME
DECLARE @To NUMERIC(10,5)

SET @From = '2009-10-11T11:00:00'
SET @To = cast(@From AS NUMERIC(10,5))

PRINT @To

This results in: 40095.45833

I am completely lost as to how 40095.45833 = 2009-10-11T11:00:00. I just do not understand the how that fixed point number equates to that source DateTime data.

Any help would be appreciated

Thank you!

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-31 : 08:34:45

Dates are really numbers that are just visually represented in date formats for people because we cannot look at 40095.45833 easily determine it is 11:00 am on 2009-10-11.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-07-31 : 08:36:47

I think internally datetime is a decimal of the number of days since Jan 01 1900 and the number of milliseconds since midnight.
Why one would want to convert a datetime to a decimal eludes me!


SELECT DATEADD(ms, 45833, DATEADD(day, 40095, '19000101'))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources