Converting String value to Datetime in SQL Server throws exception

Question

I'm struggling with converting nvarchar to date times.

I have current query:

SELECT  
    ACCT_NBR, BANK_CODE, ACCT_CODE, ACCT_CCY, HOLDER_CODE, 
    ACCT_GRP_CODE, RECEPTION_TIMES
FROM 
    VIEW_ID_MONITORING
WHERE 
    STATUS = 'EXPECTING' 
    AND ACCT_GRP_CODE = 'ID_ACME_SHIPPING_EUROPE' 
    AND CONVERT(VARCHAR(24), '2017-08-03T11:00:00+02:00', 127) = RECEPTION_START

I want to check if the RECEPTION_START is the same as today, 11'o clock with an offset of 2 hours.

But running this query throws following exception

com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.

What am I doing wrong?

You are not converting string to datetime in your query. You are converting varchar to varchar. — Peter
– Peter, Commented Aug 3, 2017 at 9:52

Zohar Peled · Accepted Answer · 2017-08-03 10:16:59Z

4

First, you are converting a varchar to varchar, as Peter commented.
Second, this kind of value can't be converted to DateTime, but it can be converted to DateTimeOffset:

SELECT CONVERT(DATETIMEOFFSET, '2017-08-03T11:00:00+02:00', 127)

Returns 03.08.2017 11:00:00 +02:00

answered Aug 3, 2017 at 10:16

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Xingzhou Liu Over a year ago

it does convert to datetime2, which truncates the +2:00. That you for making that point though, I would have never checked otherwise.

Xingzhou Liu · Accepted Answer · 2017-08-03 10:32:02Z

So your error condition is not a problem converting datetime to varchar, but rather converting varchar to datetime.

Is RECEPTION_START a datetime value? If it is, datetime has higher precedence than varchar, which means sql-server is actually trying to do the following.

CONVERT(DATETIME,CONVERT(VARCHAR(24), '2017-08-03T11:00:00+02:00', 127)) = RECEPTION_START

Thats a problem because your input string is 25 long, and varchar(24) is cutting a zero off the end and because you can't conver something with time zone info to a datetime (only datetime2 or datetimeoffset)

try instead following solution:

CONVERT(datetimeoffset,'2017-08-03T11:00:00+02:00') = RECEPTION_START

Also you'll need to check if RECEPTION_START is datetime, datetime2 or datetimeoffset. Unless it's datetimeoffset, you're comparing something with a timezone set with a value that doesn't. Make sure that's what you want. if you convert '2017-08-03T11:00:00+02:00' to datetime2, the +2:00 gets truncated.

To confirm that it's not a problem converting from your date string to varchar.

select CONVERT(VARCHAR(24), '2017-08-03T11:00:00+02:00', 127)

which works for me on both 2012 and 2014. Only issue with it is that you're setting varchar 24 type when the input string is actually 25 long. but that shouldn't matter if you use solution avbove.

Amir Schnell · Accepted Answer · 2017-08-03 09:48:22Z

0

You can either use '2017-08-03T11:00:00+02:00Z' because mode 127 expects this format:

yyyy-mm-ddThh:mi:ss.mmmZ

or use 126 instead of 127 because this expects:

yyyy-mm-ddThh:mi:ss.mmm

answered Aug 3, 2017 at 9:48

Amir Schnell

6616 silver badges13 bronze badges

Collectives™ on Stack Overflow

Converting String value to Datetime in SQL Server throws exception

3 Answers 3

1 Comment

try instead following solution:

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

try instead following solution:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related