April 16, 2014 at 12:14 pm
I am trying to take a field that has part of a date in it, so I have to parse it out as follows:
SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)
This is because a date of 04/16/2014 will show as 160416 in the first part of the field I need to parse it out of, thus becoming 04162014.
From there I then need to convert this "date" into a legitimate SQL datetime type, so that I can then run a DATEDIFF to compare it to when the record was actually entered, which is a separate field in the table, and already in datetime format.
When I use the below statement, I am getting the message that, "Conversion failed when converting date and/or time from character string."
CAST((SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)) as datetime)
I also tried CONVERT(datetime, (SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)), and got the same message.
Does anyone have any suggestions on how I can parse that field, then convert it to a datetime format for running a DATEDIFF statement?
April 16, 2014 at 12:32 pm
you have to change the order of your substrings to end up with the format yyyymmdd.
declare @date varchar(20)
set @date = '140416'
select CAST(('20' + LEFT(@date,2) + SUBSTRING(@date, 3, 2)+SUBSTRING(@date,5,2) ) as datetime)
April 16, 2014 at 12:34 pm
miles_lesperance (4/16/2014)
I am trying to take a field that has part of a date in it, so I have to parse it out as follows:SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)
This is because a date of 04/16/2014 will show as 160416 in the first part of the field I need to parse it out of, thus becoming 04162014.
From there I then need to convert this "date" into a legitimate SQL datetime type, so that I can then run a DATEDIFF to compare it to when the record was actually entered, which is a separate field in the table, and already in datetime format.
When I use the below statement, I am getting the message that, "Conversion failed when converting date and/or time from character string."
CAST((SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)) as datetime)
I also tried CONVERT(datetime, (SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)), and got the same message.
Does anyone have any suggestions on how I can parse that field, then convert it to a datetime format for running a DATEDIFF statement?
Most likely you have something in there that won't convert because it is not valid data for a datetime. This kind of manipulation is extremely brittle. One little piece of data in the wrong place and the whole thing comes crashing down. It is hard to say what the best approach for figuring out the issue. It kind of depends on how many rows are in the table, how often you need to do this process, etc. No matter the method, you are going to need to find and identify the problem rows and parse the rest of them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2014 at 12:52 pm
While in and of itself, that statement suggested works fine, but when I try to put it into a CASE statement, it is still failing. That field I am trying to parse could have NULL values, but I am only parsing those without NULL values, based on the CASE statement, and otherwise the date will default to 19000101, if it is NULL. If I take the CAST part out of the statement, then the field does show as yyyymmdd. I still need to convert it somehow for a DATEDIFF comparison against a datetime field.
CASE WHEN a1.Field1 IS NOT NULL THEN CAST(('20' + LEFT(a1.Field1,2) + SUBSTRING(a1.Field1,3,2)+ SUBSTRING(a1.Field1,5,2)) as datetime)
ELSE CAST('19000101' as datetime)
END as Date1
There are rougly 8200+ rows to account for, and this script will be part of a report, so will likely be run frequently, as one of the fields on the report is that DATEDIFF value that I am trying to calculate in number of days.
I did a spot check for any dates that may be out of the ordinary, i.e. a date of 04/32/2014, for example, and am not seeing anything such as that.
Does anyone have other suggestions, or maybe I am something that I am overlooking in my CASE statement?
April 16, 2014 at 1:33 pm
Your case expression will handle any rows with a NULL but you obviously have some data in there somewhere that cannot be cast to a datetime. Maybe you need to break your table into pieces first so you can isolate the various dateparts in your string. That way you can evaluate what your data looks like.
select LEFT(a1.Field1,2) as YearValue
, SUBSTRING(a1.Field1,3,2) as MonthValue
, SUBSTRING(a1.Field1,5,2) as DayValue
from a1
There is something in there that is invalid data for a datetime datatype. Do you have access to sql 2012? This would be a great time to use try_parse.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2014 at 2:34 pm
Unfortunately, I am not working in a SQL Server 2012 environment to use try_parse. I did run the suggested script to break apart the Year, Month, and Day, and did an ORDER BY Year, Month, and Day, where the field I am parsing IS NOT NULL. I got 268 rows that came back, and there was no dates that were out of the ordinary, such as a Month with a 13; a Day with a 32, or even a Month with an 11 (November) and Day that was 31, for example, where the date would not make sense to the system. Even a Month of 02 (February) had less than 29 for any of the correlating Day fields!
As an update...after parsing the Year, Month, and Day data, and determining that no "out of ordinary" date values existed, I put it all together in one field as such:
SELECT CONVERT(datetime, ('20' + LEFT(a1.Field1,2)) + (SUBSTRING(a1.Field1,3,2)) + (SUBSTRING(a1.Field1,5,2))) as ConvertedDate
This works just fine. So, I copied/pasted that CONVERT statement into my CASE statement as such:
CASE WHEN a1.Field1 IS NOT NULL THEN CONVERT(datetime, ('20' + LEFT(a1.Field1,2)) + (SUBSTRING(a1.Field1,3,2)) + (SUBSTRING(a1.Field1,5,2)))
This fails with the message, "Conversion failed when converting date and/or time from character string." I am not sure why the CASE statement would cause it to fail, especially since I am weeding out those values in Field1 that are NULL.
April 16, 2014 at 2:46 pm
The script you posted should work assuming the values are within acceptable values for each datepart. As much as I hate to say this I think you may have to use a while loop here and convert these one at a time to find the culprit then. Stick all the parsed values into a temp table with an identity and an extra datetime column(with all NULL). Then you can use a while loop to go through the temp table rbar and update the current row's datetime column with your parsing logic. You will find the invalid data in there somewhere.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2014 at 2:30 am
CONVERT takes a style parameter which can be used to shape the output of a date-datatype to string-datatype conversion - which is the usage that most folks are familiar with. It works for input too, describing the shape of a text string for a string-datatype to date-datatype conversion.
Your data corresponds to style 12: yymmdd. Hence, all you need to convert your data is this:
CONVERT(DATE,LEFT(MyField,6),12).
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 17, 2014 at 1:47 pm
I suggest:
1) be sure to pad the month and day values so that the final format is a full yyyymmdd.
2) apply column names to make the code easier to understand and maintain
3) use CAST() as it will always accept 'yyyymmdd' with no conversion code required.
SELECT CAST(Field1_year + Field1_month + Field1_day AS datetime)
FROM ( --dbo.tablename
SELECT '160416' AS Field1
) AS a1
CROSS APPLY (
SELECT
'20' + LEFT(a1.Field1,2) AS Field1_year,
RIGHT('0' + SUBSTRING(a1.Field1,3,2), 2) AS Field1_month,
RIGHT('0' + SUBSTRING(a1.Field1,5,2), 2) AS Field1_day
) AS ca1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 17, 2014 at 3:03 pm
All in all, upon lots of digging, I was able to find 3 records that were causing the problem. Two of the records had a dash, so trying to grab the first six characters of that field would not necessarily work. I put a case statement in place to catch those records. Then the third record had a month value of 00 and day value of 00, so I added that to the case statement to catch those values, too. Hard to find those 3 in over 8,200 records! Thank you, I appreciate everyones' input into how to troubleshoot this.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply