Date format to Int in sql server

Question

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 @Steinar varchar(200)

set @Steinar ='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(@Steinar , PATINDEX('%20[0-9][0-9]-[0-1][0-9]-[0-3][0-9]%', @Steinar ), 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

Answer 1

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())