question

Infock12 avatar image
Infock12 asked

date conversion not working with cast or convert

Hi all, I am trying to convert a date field, which is stored as nvarchar(29) and the output comes out as '2016-07-07 17:41:00'. I have tried both the below options: ,cast([CollectDT] as datetime) as Months ,convert(datetime, [CollectDT],103) as Months but I get the error message that says 'Arithmetic overflow error converting expression to data type datetime' I added 'set dateformat dmy;' when i tried the cast function too. Still the same message. Please help. Thanks,
convertcast
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

put the where clause (`where isdate(CollectDT) = 1`) into your query that uses cast/convert so it only pulls rows that can be cast/converted The other 240,000 rows do not contain valid dates. You will need to fix those in order to treat them as dates. In the future, I would recommend storing date/time information in a datetime or datetime2 column.
1 Like 1 ·
That sample value seems to work fine. You appear to have other rows in that column that are not valid dates. What do you get with this query? select * from where isdate(CollectDT) = 0;
0 Likes 0 ·
Hi Ken, Thanks for the reply. Yes, you may be right. When I use the where statement, it is giving me around 460,000 rows whereas there are more than 700,000 rows. Is there a way of tackling this please? Thanks, Vincent
0 Likes 0 ·
Great, thank you very much Ken, that is very helpful
0 Likes 0 ·

1 Answer

· Write an Answer
Wilfred van Dijk avatar image
Wilfred van Dijk answered
not knowing which SQL version you have, but there's also a TRY_CAST() function which returns NULL if the conversion fails, for example `select try_cast('20160202' as date)` will give a date, but `select try_cast('20160231' as date)` won't
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Wilfred, thanks for your reply. Mine is a 2008 version and unfortunately, this function seems to be working only from version 2012 and above, but thank you very much for the response.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.