Multiple Options to Force Datetime to Bigint

I don’t need this in my coding but the multiple ways to do it can demonstrate how you can use the language.


declare @dt datetime ='2017-06-08 03:31:39.167'

declare @dt datetime ='2017-06-08 03:31:39.167'

--Option 1
Select Cast(year(@dt)*10000000000
+ month(@dt)*100000000 
+ day(@dt)*1000000 
+ datepart(hour, @dt)*10000 
+ datepart(minute,@dt)*100 
+ datepart(second,@dt)*1
 as bigint)

 --Option 2
select CAST(concat(year(@dt)
,Right('0'+Cast(month(@dt) as varchar(2)),2)
,Right('0'+Cast(day(@dt) as varchar(2)),2)
,Right('0'+Cast(datepart(hour, @dt) as varchar(2)),2)
,Right('0'+Cast(datepart(minute,@dt) as varchar(2)),2)
,Right('0'+Cast(datepart(second,@dt) as varchar(2)),2)
) as BIGINT)
 --Option 3
 select Cast(Convert(varchar(8),@dt,112)+Replace(Convert(varchar(8),@dt,114),':','') as BIGINT)

 --option 4

 Select CAST(FORMAT(@dt,'yyyyMMddHHmmss') AS bigint) 

  --Option 5
 select  Cast(Replace(Replace(Replace(Convert(varchar(19),@dt,121),' ',''),'-',''),':','')  as BIGINT)

   --Option 6
 select  Cast(Replace(Replace(Replace(Convert(varchar(19),@dt,121),' ',''),'-',''),':','')  as BIGINT)
  --Option 7
 select Cast(Convert(varchar(8),@dt,112)+stuff(stuff(Convert(varchar(8),@dt,114),3,1,''),5,1,'') as BIGINT)
  --Option 8
 select Cast(Convert(varchar(8),@dt,112)
 + substring(Convert(varchar(8),@dt,114),4,2)
 + substring(Convert(varchar(8),@dt,114),7,2)) as BIGINT)


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s