How to get today date at night 12:00AM in milliseconds using MSSQL query

Transact-SQL
1

Hi,

How to get today date in milliseconds using MSSQL query.

That means I want to get today date , example : 6th Nov 2015 Night 12:00AM in milliseconds format.

Please help me

-Thanks
Nagendra

2

Can you give an example? What would 6th Nov 2015 Night 12:00AM look like in millisecond format? Is it the number of milli seconds after midnight that you are trying to get?

3

Epoch timestamp: 1446748200
Timestamp in milliseconds: 1446748200000
Human time IST: 06/11/2015, 00:00:00
Human time (GMT): Thu, 05 Nov 2015 18:30:00 GMT

Its like unix epoch time.
For more help: please refer http://www.epochconverter.com/

-regards
Nagendra

4

If you had said the word "epoch" that would have made it clearer :smile:

 CAST(DATEDIFF(SECOND,'19700101',GETDATE()) AS BIGINT)*1000;

Replace GETDATE() with whatever date you want - for midnight of Nov 6, 2015, use '20151106'

5

It's helped me. Thank You very much.

-regards
Nagendra

6

If you are using SECOND in the DATEDIFF function, you'll always be losing precision. Is that what you want? It seems out of place to require precision down to the millisecond and then throw away 999 possible values (or at least 333 possible values)

7

Here due to GETDATE(), above query is giving the current time in milli secinds format.

But i want start time(for example 10th Nov 2015 00:00:00) of the day in milliseconds. please help me to solve.

-regards
Nagendra Rednam

8

Look at the "CAST and CONVERT (Transact-SQL) " in BOL.
See CONVERT with style 113

9

CONVERT(VARCHAR(24),GETDATE(),113) gives the time upto milli seconds as'10 Nov 2014 11:45:34:243'

But I want the today date (Nov 10 2015 00:00:00) in milli seconds format as Epoch timestamp format.

James helped me with 'CAST(DATEDIFF(SECOND,'19700101',GETDATE()) AS BIGINT)*1000;' query.

But this is giving present time in Epoch timestamp format.

-regards
Nagendra

10

Works fine with below query:

DECLARE @Date datetime;SET @Date = CAST(GETDATE() AS DATE);select CAST(DATEDIFF(SECOND,'1970-01-01', @Date) AS BIGINT)*1000 as LogTimeStamp

-regards
Nagendra