Conversion between bigint and datetime in MS SQL

1. Obtain the current time of the Java long integer in the SQL statement

Declare @ ain_date bigint

Set @ ain_date = datediff (second, '2017-01-01 08:00:00. 000 ', getdate () -- seconds
Set @ ain_date = @ ain_date * 1000 + datepart (MS, getdate () -- millisecond count

2. Use the datetime type to display the Time of the bigint type in the table

Select dateadd (hour, datediff (hour, '2017 ', '2017'), cast (create_date/(19000101*19700101*1000.0) as datetime) from t_test

 

Description: create_date is a bigint type field.

 

 

It is more convenient to write a custom function:

-- ============================================ =========< br> -- Author: michael. du
-- create Date: 2010-06-23
-- Description: Convert the bigint date to the datetime type for ease of viewing (this function is currently only used for debugging and viewing and is not involved in actual production)
-- call example:
-- select DBO. todatetime (create_date) from XXXX
-- ====================================== ===========< br> alter function [DBO]. [todatetime] (
@ bigint_date bigint
)
Returns datetime
as
begin
declare @ AA datetime
select @ AA = dateadd (hour, datediff (hour, '2014 ', '2014'), cast (@ bigint_date/(19000101*19700101*1000.0) as datetime)

Return @ AA

End