this has really baffled me for a few days and am seeking
help. I am trying to handle strings from a free format field and either convert them to a date in the format of "dd/mm/yyyy" or if they are not in this format then simply display the text verbatim.
I need this in a VIEW so can not use SET LANGUAGE. Sounds simple using convert and isdate but does not seem to work.
So for the snippet of code below (remember this will be in a VIEW) I want to read the text and if the string converts to a date (ie. is in the format of "dd/mm/yyyy" then run the convert to a date as I need it in date format for Excel to pick up (via Connect SQL Sever Database)), and if it does not convert to a date then display the text as it is.
create table dateTest1 ( idx int, dateStringTest varchar(15) ); insert into dateTest1 (idx, dateStringTest) values (1, '13/01/2021'), (2, 'no'); select case when isdate(convert(datetime, dateStringTest, 103)) = 1 then convert(datetime, dateStringTest, 103) else dateStringTest end as dtres from dateTest1 --where idx = 1
-- error: Msg 241, Level 16, State 1, Line 15 Conversion failed when converting date and/or time from character string.
-- this error happens for idx = 2. Idx = 1 works ok
Any assistance with this would be greatly appreciated as it's doing my head in.
Thanks in advance