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.
I had to change the Database it to 2012 compability and try_convert then was recongnised.
However try_convert does not solve the issue. I know when it fails and when it works. But dont know how to solve.
when i insert other dates that are compatible with "mm/dd/yyyy" (ie. the first part of the date is between 1 - 12) then the statement seems to work. But in Australia our format is dd/mm/yyyy and dates where the dd is greater than 12 then it fails.
I cant use SET LANGUAGE in a VIEW.
Not sure how to tell the VIEW that the string is in a format of dd/mm/yyyy not mm/dd/yyyy.
Thanks yosiasz - that is my issue. The dates entered in to a varchar field in the database are all in the format dd/mm/yyyy as that is our standard in Australia. When i use try_convert (datetime, xxxx, 103) it still fails saying "Msg 241, Level 16, State 1, Line 24
Conversion failed when converting date and/or time from character string."
I want to convert the string entered into an actual date format (so Excel will show it as date). My DB thinks its in mm/dd/yyyy but i want it to think it's dd/mm/yyyy. SET LANGUAGE and SET DATEFORMAT dmy; does not work in a VIEW.
Any assistance would be appreciated as it's doing my head in
I'm trying to convert the following strings (as the field the data is has the of type char not date as it may contain non-dates. For eg. the field contains the following strings:
'03/06/2021'
'28/10/2022'
'No'
'15/01/2021'
'No'
NOTE - the dates are all in dd/mm/yyyy so first one is 3rd June. The field may contain NON-date strings such as "No".
The issue is in my Excel when i get data from this VIEW, it shows the text as "string" not as DATE for those that are actually dates. So my dashboard (again in Excel) is not calculating the correct info as it does not see this field as Dates.
all dates are in dd/mm/yyyy so 03/02/2021 would be 3rd Feb, 2021.
This date will be passed by try_convert as it is treated by the system 2nd March, but i dont want it as 2nd March i want it as 3rd Feb.
Secondly if the date was 28th Oct (28/10/2021) the convert will fail because it thinks it's an invalid date
create table #dateTest1
( idx int identity(1,1),
dateStringTest varchar(15)
);
insert into #dateTest1 ( dateStringTest)
values ('13/01/2021'), ('no'),
('03/06/2021'),
('28/10/2022'),('15/01/2021')
select TRY_CONVERT(char(10),dateStringTest, 103) _103,
case
when TRY_CONVERT(char(10),dateStringTest, 103) is null then dateStringTest
else CONVERT(char(10), dateStringTest, 103)
end
from #dateTest1
drop table #dateTest1
Thanks for your efforts yosiasz. This does not result in an error which is a great start.
But I am looking to have the output in datetime format not as varchar - so Excel can use as dates.
Ideally I want: "else CONVERT(datetime, dateStringTest, 103)" but this fails with "conversion failed when converting date and/or time from character string". The error is for rows 1, 4 and 5 as the system thinks the date is in mm/dd/yyyy and cant convert the dates i have. SO i need to tell the system the dates are in dd/mm/yyyy so i can use convert(dateime). I thought the 103 parameter does that but it does not seem to work.
Ha - so you can see how frustrating this is
thanks again
Paul
create table #dateTest1
( idx int identity(1,1),
dateStringTest varchar(15)
);
insert into #dateTest1 ( dateStringTest)
values ('13/01/2021'), ('I am not a date'),
('03/06/2021'),
('28/10/2022'),('15/01/2021')
select TRY_CONVERT(char(10),dateStringTest, 103) _103,
case
when TRY_CONVERT(date,dateStringTest, 103) is null then dateStringTest
else FORMAT (CONVERT(date,dateStringTest , 103), 'dd/MM/yyyy')
end as sweet
from #dateTest1
drop table #dateTest1
Haha - happy to send you that. Thanks again - i do appreciate your assistance and efforts with this.
Unfortunately the issue remains. I removed all the non-date strings (in our case they were all the "No" fields. Set them to NULL to see if it makes a difference. No luck.
The issue is this - i've narrowed it down.
if i convert (date, '13/01/2021', 103) - this shows up as a date format field in Excel - perfect
If i convert(date, dateStringTest, 103) - this shows up a a string format in Excel. That is the issue.
THe hardcoding of 13/01/2021 is ok, but when the variable is set to 13/01/2021 it shows as string
