SQLTeam.com | Weblogs | Forums

Convert string to date

Hi all

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
Paul

CONVERT is trying to convert to datetime BEFORE the isdate check happens.

You need to use TRY_CONVERT first instead to test whether the column contains a valid date before converting it.

Thanks 'ScottPletcher' for you suggestions.

However when I try 'TRY_CONVERT' I get the following:

Msg 195, Level 15, State 10, Line 18
'TRY_CONVERT' is not a recognized built-in function name.

I am using SQL SERVER 2017

I tried this:

select
case when TRY_CONVERT(DATE, dateStringTest)
then convert(datetime, dateStringTest, 103)
else dateStringTest
end as dtres
from dateTest1

What version of microsoft sql server are you on?

Hmm, is your db compatibility level below SQL 2012? I think TRY_CONVERT was added in 2012.

Make sure you are running the query in a db with a compat level of at least 2012.

Thanks - I'll look into this. Not sure what impact to the rest of the DB if I make this change

I am running MS SQL SERVER 2017

Thats why TRY_CONVERT is not working.

Thanks

TRY_CONVERT works on SQL 2012 and all later versions. I'm not sure what you mean by your last comment.

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

You provided the following data

13/01/2021

That is not mm/dd/yyyy format

I dont know of any place that has 13 months except Ethiopia

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 :slight_smile:

Thanks
Paul

Nah yaay, yaay nah. learned those on my trip down under. Ok lets do this

So if what you want is dd/mm/yyyy why are you trying to convert '13/01/2021'? it is already dd/mm/yyyy

haha - thanks and good luck.

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.

Hope this clarifies.

Thanks again
Paul

Clearer. What if you have 03/02/2021

What ia that date?

March 2
Or Feb 3

Is there another column indicating country of origin

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

No other column for country

Maybe this?

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 :slight_smile:
thanks again
Paul

Just send me a vegemite sandwich


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
1 Like

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

Strange

Paul

your issue is not sql, it is Excel. What happens in excel that you consider problematic? One of the values is string so it is defaulting to that