如何在sql server中将bigint转换为datetime [英] how to convert bigint to datetime in sql server

问题描述

您好，



我在bigint中有一个值，我需要将其转换为datetime我的值是19820201150000我试过这些解决方案但不是单一的解决方案正在运行。

Hello,

I have a value in bigint and i need to convert it into datetime my value is this "19820201150000" i tried these solutions but not a single solution is working.

SELECT DATEADD(SECOND, 1218456040709 / 1000, '19691231 20:00')

SELECT DATEADD(SECOND, 19820201150000 / 1000, '19691231 20:00')

select dateadd(hh,-4,dateadd(ss,19820201150000/1000,'1970/01/01 00:00'))

select CONVERT(DATETIME,REPLACE(DATEADD( day, 19820201150000, '12/31/1849'),'12/31/1849',0),101)

select dateadd(ss, 19820201150000/1000, '19691231 20:00')

SELECT DateAdd(ms, 19820201150000 % 1000, DateAdd(SECOND, 19820201150000 /  1000, '1970-01-01T00:00:00.000')) AS ToTheMs,
   DateAdd(SECOND, 19820201150000 /  1000, '1970-01-01T00:00:00.000')  AS ToTheSecond,
   DateAdd(MINUTE, 19820201150000 / 60000, '1970-01-01T00:00:00.000')  AS ToTheSecond

推荐答案

检查以下网址

http：// stackoverflow.com/questions/3650320/convert-from-bigint-to-datetime-value

Check the below url
http://stackoverflow.com/questions/3650320/convert-from-bigint-to-datetime-value

检查这个

check this

DECLARE @di_date BIGINT
SET @di_date=19820201150000

DECLARE @ds_date VARCHAR(20)
SET @ds_date=CONVERT(VARCHAR(20), @di_date )

select
Convert(time,Dateadd(SECOND,
        Right(@ds_date,2)/1,
        Dateadd(MINUTE,
                Right(@ds_date,4)/100,
                Dateadd(hour,
                        Right(@ds_date,6)/10000,
                        '1900-01-01')))) +
convert(datetime,LEFT(@ds_date,8)) as DateConverted

学分http://rdineshkumar.wordpress.com/tag/how-to-convert-yyyymmddhhmmss-to-datetimedatetime-in-sql-server

credits http://rdineshkumar.wordpress.com/tag/how-to-convert-yyyymmddhhmmss-to-datetimedatetime-in-sql-server

First please check with this 1982020115000.  (i removed one zero what you gave in the question because as per my knowledge that value should be 13 digits) now it is working
SELECT DATEADD(SECOND, 1982020115000 / 1000, '19691231 20:00')

这篇关于如何在sql server中将bigint转换为datetime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！