date conversion not working with cast or convert

Question

question

Infock12 asked Oct 25, '16

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,

convert cast

4

10 |1200

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

KenJ commented · Oct 25, 2016 at 10:22 PM

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 ·

KenJ commented · Oct 25, 2016 at 02:55 PM

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 ·

Infock12 commented · Oct 25, 2016 at 04:06 PM

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 ·

Infock12 commented · Oct 27, 2016 at 11:25 AM

Great, thank you very much Ken, that is very helpful

0 ·

Answer 1 · 2016-10-27T13:13:13Z

Wilfred van Dijk answered Oct 27, '16

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

10 |1200

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

Infock12 commented · Oct 31, 2016 at 05:23 PM

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 ·

question