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

convert date object into integer Expand / Collapse
Author
Message
Posted Monday, September 29, 2008 12:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:46 AM
Points: 1,109, Visits: 279
I continue without understand what you need exactly.

Anyway, to obtain the number of miliseconds from a datetime value, you can use something like this:

declare @Date DATETIME

SET @Date = convert(datetime, '2009-09-01 09:30:15.927')


SELECT convert(BIGINT, @Date)* 86400000

SELECT DATEPART(hh, @Date) * 3600000
SELECT DATEPART(mi, @Date) * 60000
SELECT DATEPART(ss, @Date) * 1000
SELECT DATEPART(ms, @Date)


SELECT (convert(BIGINT, @Date)* 86400000 ) + (DATEPART(hh, @Date) * 3600000) + (DATEPART(mi, @Date) * 60000) + (DATEPART(ss, @Date) * 1000) + DATEPART(ms, @Date)


And, the final result is:

Datetime: 2009-09-01 09:30:15.925
Number of milisenconds: 3460786215927


I hope this can help you.
Post #577540
Posted Monday, September 29, 2008 12:46 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
If it is a matter of finding the difference between two dates (in milliseconds) you can even run the following query.
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT @d1 = '1/9/2008 11:30:24.123', @d2 = '1/10/2008'

SELECT DATEDIFF(millisecond, @d1, @d2) AS Diff
/*
Diff
-----------
44975877
*/

Or you can extract specific information from the date value using the following query and apply your required calculations.
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT @d1 = '1/9/2008 11:30:24.123'

SELECT
DATEPART(year, @d1) AS Years,
DATEPART(month, @d1) AS Months,
DATEPART(day, @d1) AS Days,
DATEPART(hour, @d1) AS Hours,
DATEPART(minute, @d1) AS Minutes,
DATEPART(second, @d1) AS Seconds,
DATEPART(Millisecond, @d1) AS Milliseconds

/*
Years Months Days Hours Minutes Seconds Milliseconds
----------- ----------- ----------- ----------- ----------- ----------- ------------
2008 1 9 11 30 24 123
*/



.
Post #577543
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse