Converting DATETIME to NUMERIC

Question

Converting DATETIME to NUMERIC

Leonid Yatsenko

SSC Veteran

Points: 207
More actions
June 14, 2004 at 3:50 am

#108397

Hi, All!
My question is: what's the way T-SQL makes subj? CONVERT gives me value, in which integer part represents date (as i suppose), but what is 12-digit fractional part? I don't know, because difference in 1 millisecond gives no difference in the result of converting, while difference in 2 millisecond gives huge difference (5 last digits)! Help me please!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The integer portion of a datetime is the date and the fractional is the time. I don't know the details of how it's stored though.

What are you trying to do that you need to work with a date in numeric format?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 2

DECLARE @dt DATETIME

Set @dt = '1/1/1901 12:01'

Select Convert(int,@dt)

My Blog: http://dineshasanka.spaces.live.com/

Leonid Yatsenko SSC Veteran Points: 207 More actions · Answer 3

I need to calculate some agregate function for any period of time. For ex: find sum value of parameter for every 1h 23min 34sec starting at 2004-01-02 21:12:78.000

I want to convert values in the datetime column to numeric (some integer, at the end), as well as period of summarizing, divide and get an integer part (/), and than group by that integer part...

May be, it's not too clever, but it is what i have thought out...

Any alternatives?

David Burrows SSC Guru Points: 65142 More actions · Answer 4

This will give you the start and end datetime for 10 intervals from the start datetime

declare @start datetime,@interval int

set @start = '2004-01-02 21:12:58.000'

set @interval = datediff(ms,'','1900-01-01 01:23:34')

select number,

dateadd(ms,(number -1) * @interval,@start) as [periodfrom],

dateadd(ms,number * @interval,@start) as [periodto]

from master.dbo.spt_values

where type = 'P'

and number >= 1

and number <= 10

you can then join this to your data to see which interval the data belongs.

This solution would enable you to generate a max of 255 intervals. To cater for more, I would suggest using a temp table and generate as many intervals as required.

Far away is close at hand in the images of elsewhere.
Anon.

David Burrows SSC Guru Points: 65142 More actions · Answer 5

ps

To answer your question, the following is from BOL

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

When you convert to numeric the decimal portion is the number of milliseconds since midnight as a fraction of a day (86400000 ms) expressed as a decimal, eg 12:00:00 would be .5

Far away is close at hand in the images of elsewhere.
Anon.

Leonid Yatsenko SSC Veteran Points: 207 More actions · Answer 6

Many thanks!

I've remembered datediff too! And you are more powerfull user of BOL, for i havn't found this article!