question

newbee avatar image
0 Votes"
newbee asked ErlandSommarskog commented

Date format to Int in sql server

Hi All,

I have a long string like below in my column and I am getting date from that.
My issue is when I am converting that to integer as per my requirement I am not getting the exact output

Example :

declare @str varchar(200)

set @str ='Name - Koteswararao vp Department - Prod Location - Hyderabad Domain - ND Job Title - Technical-ABAP Date of Hire - 2021-07-05 Vendor ID - v2345 Candidate ID - Associate ID - 3334 Name - Hardware Allocation'


select convert(bigint,dateadd(hour,3,substring(@str, PATINDEX('%20[0-9][0-9]-[0-1][0-9]-[0-3][0-9]%', @str), 10) ))


I ran this and getting output as 44380
If I convert this to date using google , its showing as Thursday, January 1, 1970
But its actual date is Monday, July 5, 2021

Could any one please help

sql-server-generalsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


What result do you expect and how do you define such conversion? For example, what is the number today?


0 Votes 0 ·

Hello Viorel,

For today the number is 1627359413 (2021-07-27 00:16:53.000)

I need it in number format as I am using this in one of the function calculations, where other dates are in int format

0 Votes 0 ·

Just as information: 44380 is the number of days since 1900-01-01, or more exactly day 1 in SQL Server is 1900-01-01.

There are more than one convention for having numbers at dates. The one you want is a Unix date.

0 Votes 0 ·

1 Answer

Viorel-1 avatar image
1 Vote"
Viorel-1 answered

Check if these queries give the expected numbers:

 select datediff_big(second, '1970-01-01', dateadd(hour, 3, substring(@str, PATINDEX('%20[0-9][0-9]-[0-1][0-9]-[0-3][0-9]%', @str), 10) ))
 select datediff_big(second, '1970-01-01', getdate())


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.