question

newbee avatar image
0 Votes"
newbee asked EchoLiu-msft edited

Datetime to Int in sql server

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 @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 dateadd(hour,14,cast(substring(@string, PATINDEX('%20[0-9][0-9]-[0-1][0-9]-[0-3][0-9]%', @string), 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.

sql-server-generalsql-server-transact-sql
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.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

The simplest answer is to use "TRY_CAST":

 where dateadd(hour,14,try_cast(substring...
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.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

Datetimes are not an integers.

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

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.

newbee avatar image
0 Votes"
newbee answered

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.

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.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.








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.

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered EchoLiu-msft edited

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:
118103-image.png


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



image.png (1.3 KiB)
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.