Tag: integer

SQL Server datetime to int

birds

declare @currentDateTime datetime 
declare @timeStampValue int 

set @currentDateTime = SYSDATETIME(); 
print @currentDateTime 
set @timeStampValue = DATEDIFF(second, '1970-01-01', @currentDateTime) 
print @timeStampValue

 
To check the result:

https://www.epochconverter.com/

 

UTC milleseconds example:

 
declare @timeStampValue Bigint  
declare @utcDateTime datetime 

set @utcDateTime = GETUTCDATE(); 
set @timeStampValue = DATEDIFF(SECOND, '1970-01-01', @utcDateTime) 
set @timeStampValue = @timeStampValue * 1000

 

Advertisements
SQL – Find the largest number in a set of alpha-numeric values

SQL – Find the largest number in a set of alpha-numeric values

References:

The SQL:

declare @lastIntUsed int 

create table #temp (clean_number varchar(50), IntCol int) 
; with tally as (select top (100) N=ROW_NUMBER() over (order by @@spid) from sys.all_columns),
data as ( 
   select sometable.number, clean_number
   from sometable
   cross apply ( 
      select (select C + '' FROM (select N, SUBSTRING(sometable.number, N, 1) C  
          from tally
          where N<=DATALENGTH(sometable.number)) [1]
      where C between '0' and '9'
      order by N
      for xml path('')) 
   ) p (clean_number)
   where p.clean_number is not null 
) 
insert into #temp (clean_number, IntCol) 
select number, cast(clean_number as int) IntCol from data

set @lastIntUsed = (select MAX(IntCol) from #temp) 
print @lastIntUsed
drop table #temp

I couldn’t find any way to just get the max number from the CTE itself (doesn’t mean there isn’t a way), so I said screw it – I’ll just stick the results into a temp table and then I can get the max number or the min or the max number that is less than some value or whatever I want.