convert datetime to bigint

Question

is there a function to convert datetime to bigint in sql? E.g. I have a date "2010-11-02 00:00:00" and i would like to get result as number "20101102000000"

Or do I need to write some custom function which will remove -,space,: from datetime?

That doesn't really make sense. You mean you want it as a string. Formatted like: "yyyyMMddHHmmss"? - Just out of interest, why do you need it as a big int? — Lee Gunn, May 10 '11 at 12:22
@Lee Gunn - not necessarily, you could decide to store your dates as "integers" and assume the "format" of the number, i.e. to way to interpret it, to be like yyyyMMddHHmmss. That is the last to digits are the seconds, the next two are the minutes, etc. — Christian.K, May 10 '11 at 12:34
but a date is already a big int...it's the number of ticks (or whatever) since the epoch. — Lee Gunn, May 10 '11 at 12:36

score 3 · Accepted Answer · edited May 23 '17 at 12:04

3

you can try this code here..

update Table_1 set xnew=REPLACE(REPLACE(REPLACE (CONVERT(VARCHAR,x,20) , '-' , '' ),':',''),' ','')

but this is duplicate question I answered it here too : Sql datatype conversion

edited May 23 '17 at 12:04

Community

1
1

answered May 10 '11 at 12:26

Adnan M. TURKEN

1,554
4
21
36

1

You'd want 120 as the conversion number for the 4-digit year. You'd also want to cast the whole number as an int – James Wiseman May 10 '11 at 12:27

score 1 · Answer 2 · answered Oct 03 '17 at 23:49

1

The solution I have been using lately (in SQL 2008 R2) is:

cast(CONVERT(nvarchar(8),CURRENT_TIMESTAMP,112) as bigint)

The number of characters in the nvarchar limits the precision. I only need the date.

answered Oct 03 '17 at 23:49

Andrew Corke

31
2

score 1 · Answer 3 · answered May 10 '11 at 12:28

1

You cannot convert '2010-11-02 00:00:00' to 20101102000000 in T-SQL directly so yes, as you say, you would need to modify the string into something you can cast as a bigint.

declare @str varchar(19) = '2010-11-02 00:00:00'
select cast(replace(replace(replace(@str, '-', ''), ' ', ''), ':', '') as bigint)

answered May 10 '11 at 12:28

Alex K.

171,639
30
264
288

But @Pramodtech starts with a `DATETIME`, not a string. What you have here would work after string conversion. – eksortso May 10 '11 at 12:51
Ah good point my eyes just skipped to the quotes and I assumed a string – Alex K. May 10 '11 at 13:00

score 0 · Answer 4 · answered May 10 '11 at 13:00

Here is a solution:

declare @t datetime = '2010-11-02 00:00:00'

select  DATEPART(second, @t) +
        DATEPART(minute, @t) * 100 +
        DATEPART(hour, @t) * 10000 +
        DATEPART(day, @t) * 1000000 +
        DATEPART(month, @t) * 100000000 +
        DATEPART(year, @t) * 10000000000

convert datetime to bigint

4 Answers4

Linked