Datetime to Int in sql server

Question

I have datetime field where I need to convert to int.
I tried using convert(int,filed) ,but getting below error.
Please help

Below is my date filed: (You can copy and run it)

declare @ hide varchar(200) = '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 dateadd(hour,14,cast(substring(@ hide , PATINDEX('%20[0-9][0-9]-[0-1][0-9]-[0-3][0-9]%', @ hide ), 10) as datetime))

Error :

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '2021-07-05' to data type int.

Answer 1

You likely have bad data in your [description] field.

The simplest answer is to use "TRY_CAST":

where dateadd(hour,14,try_cast(substring...

Answer 2

When I run your statement, I don't get any error, but I get the response 2021-07-05 14:00:00.000.

However, I note that the date in the string is on the format YYYY-MM-DD, and you are converting to datetime. Convert to datetime2(3) to make your life easier. When converting YYYY-MM-DD to datetime2(3), it is always interpreted as such. But when converting to datetime, the interpretation depends on the DATEFORMAT setting and may be interpreted as YYYY-DD-MM instead.

Answer 3

Hi @vissupangam-0394,

Welcome to the microsoft TSQL Q&A forum!

Your question is related to tsql, so my colleague helped you add the tsql tag.

Declare @string varchar(200) = '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  * FROM  
(SELECT dateadd(hour,14,cast(substring(@string,   
PATINDEX('%20[0-9][0-9]-[0-1][0-9]-[0-3][0-9]%', @string), 10) as datetime)) dd)t  
WHERE dd between '2021-07-01 00:00:00.000' and '2021-07-18 00:00:00.000'  

Output:

I tested the above code and it returned the result I expected.And I double-checked the method you got in the previous question, it is correct.So, as Tom mentioned, it is possible that the data extracted from your long string contains an incorrect date format.You can refer to the method provided by Tom and try try_cast(TRY_CAST (Transact-SQL)).

If you have any question, please feel free to let me know.

Regards
Echo

Answer 4

Datetimes are not an integers.

What exactly are you expecting for a value when you convert '2021-07-05' to an integer?

Answer 5

Hey Tom,

You can ignore that.
Actually I am using my datetime filed in where condition and getting below error.

where dateadd(hour,14,cast(substring(cr.[description], PATINDEX('%20[0-9][0-9]-[0-1][0-9]-[0-3][0-9]%', cr.[description]), 10) as datetime)) between '2021-07-11 00:00:00.000' and '2021-07-18 00:00:00.000'

Error :

Conversion failed when converting date and/or time from character string.