CODEWITHSUNDEEP
sql-server
pramodtech
pramodtech

Reputation: 6270

convert datetime to bigint

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?

Upvotes: 2

Views: 27202

Answers (6)

Stephen Falken
Stephen Falken

Reputation: 111

I had the same question when I was generating dummy data to test an ETL process, and I needed a way to generate values that are sequential, constantly ascending, not an identity column, can be cast to binary(10), and can be used in place of an LSN value in a makeshift CDC. newsequentialid() doesn't work because it needs to be the DEFAULT for a column.

Note: Adding any extra "f"s will result in arithmetic overflow.

select cast(format(sysdatetimeoffset(), 'yyyyMMddHHmmssfffff') as bigint)

Upvotes: 0

user25505238
user25505238

Reputation: 1

Cast((date(RCRE_TIME)) AS bigint)

Upvotes: 0

Andrew Corke
Andrew Corke

Reputation: 31

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.

Upvotes: 1

Johan
Johan

Reputation: 1192

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

Upvotes: 0

Alex K.
Alex K.

Reputation: 175748

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)

Upvotes: 1

Adnan M. TURKEN
Adnan M. TURKEN

Reputation: 1594

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

Upvotes: 3

Related Questions