October 18, 2017 at 8:22 am
I imported a csv file that has a column with dates. The column is a varchar type and it has different format dates(see below). I created a new colum as datetime. I want to update the new datetime column with the values in the varchar column. How can i do this?
03/15/2017
14.06.15
27.01.2014
24th May 2
09.09.2015
42550
27.07.16
October 18, 2017 at 8:31 am
Get the original source to be correct... If those are the dates you have to deal with there's no point trying to convert it, and it's going to end up wrong.
A few reasons why:
You have the dates 14.06.15 and 03/15/2017. That means that you have both ddMMyyyy and MMddyyyy formats in there. If I gave you the date 01.02.17 what is the answer: 01 February 2017 or 02 January 2017? Which ever one you pick, the answer is the other.
"24th May 2" What does "2" represent? Is it really the year 2 AD? That's 2015 years ago!
Can I assume 42550 is the excel Integer value for 29 June 2016, or does it represent the value of date 0 (in SQL Server) + 42550 = 01 July 2016.
Too many of your dates can't be determined, or at least reliably. You need to fix the source of the data before you can do anything with this. That, or hire a "monkey" to work its way through every single record and fix them.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 18, 2017 at 10:27 am
Source is what it is... I can't change that. I also don't see why the date column be updated in several parts..updating first the dd/mm/yyyy formats and then the dd.mm.yy format...etc... Some may be skipped, but I think I can get most. Do I not update the column because I can't get 100% ?? doesn't make sense. I want to get as much as I can.
03/15/2017 (mm/dd/yyyy)
14.06.15 (dd.mm.yy)
27.01.2014 (dd.mm.yyyy)
24th May 2
09.09.2015 (dd.mm.yyyy)
42550
27.07.16 (dd.mm.yy)
October 18, 2017 at 11:15 am
I created a smalldatetime column (PTL_StartDate_Dt) and I tried running this update and did not work for me:
PTL_StartDate is a varchar column with values
01/15/2017
11/22/2016
Update [Promo]
set PTL_StartDate_Dt = Convert(Date, PTL_StartDate, 101 )
Where CHARINDEX('/', PTL_StartDate) > 0
October 18, 2017 at 11:27 am
GrassHopper - Wednesday, October 18, 2017 11:15 AMI created a smalldatetime column (PTL_StartDate_Dt) and I tried running this update and did not work for me:PTL_StartDate is a varchar column with values
01/15/2017
11/22/2016Update [Promo]
set PTL_StartDate_Dt = Convert(Date, PTL_StartDate, 101 )
Where CHARINDEX('/', PTL_StartDate) > 0
What happened when you tried the update?
I just quickly created a promo table with columns as you've stated them and the values and used your update and returned PTL_StartDate_Dt values of
2017-01-15 00:00:00
2016-11-22 00:00:00
October 18, 2017 at 11:30 am
SQLPirate - Wednesday, October 18, 2017 11:27 AMGrassHopper - Wednesday, October 18, 2017 11:15 AMI created a smalldatetime column (PTL_StartDate_Dt) and I tried running this update and did not work for me:PTL_StartDate is a varchar column with values
01/15/2017
11/22/2016Update [Promo]
set PTL_StartDate_Dt = Convert(Date, PTL_StartDate, 101 )
Where CHARINDEX('/', PTL_StartDate) > 0What happened when you tried the update?
I just quickly created a promo table with columns as you've stated them and the values and used your update and returned PTL_StartDate_Dt values of2017-01-15 00:00:00
2016-11-22 00:00:00
I changed PTL_StartDate_Dt from SmallDateTime to Date.
Then, I tried the update again:
Update [Promo]
set PTL_StartDate_Dt = Convert(Date, PTL_StartDate, 101 )
Where CHARINDEX('/', PTL_StartDate) > 0
I get this error msg:
Conversion failed when converting date and/or time from character string.
October 18, 2017 at 11:34 am
Also, I added all the values you supplied initially to Promo and ran this update
update [Promo]
set PTL_StartDate_Dt = Convert(Date, PTL_StartDate, 101 )
WHERE isdate(ptl_startdate) = 1
It updated for '03/15/2017' and '09.09.2015'. You might be able to use isdate to knock out a good handful of your values right off the bat and then only have to code for the rest of the unknowns.
October 18, 2017 at 11:41 am
GrassHopper - Wednesday, October 18, 2017 11:30 AMSQLPirate - Wednesday, October 18, 2017 11:27 AMGrassHopper - Wednesday, October 18, 2017 11:15 AMI created a smalldatetime column (PTL_StartDate_Dt) and I tried running this update and did not work for me:PTL_StartDate is a varchar column with values
01/15/2017
11/22/2016Update [Promo]
set PTL_StartDate_Dt = Convert(Date, PTL_StartDate, 101 )
Where CHARINDEX('/', PTL_StartDate) > 0What happened when you tried the update?
I just quickly created a promo table with columns as you've stated them and the values and used your update and returned PTL_StartDate_Dt values of2017-01-15 00:00:00
2016-11-22 00:00:00I changed PTL_StartDate_Dt from SmallDateTime to Date.
Then, I tried the update again:
Update [Promo]
set PTL_StartDate_Dt = Convert(Date, PTL_StartDate, 101 )
Where CHARINDEX('/', PTL_StartDate) > 0I get this error msg:
Conversion failed when converting date and/or time from character string.
Weird. And the only two values are the two you provided? I can throw both of those values directly into an update of PTL_StartDate_Dt without a CONVERT and it takes them just fine.
October 18, 2017 at 11:49 am
GrassHopper - Wednesday, October 18, 2017 11:30 AMI changed PTL_StartDate_Dt from SmallDateTime to Date.Then, I tried the update again:
Update [Promo]
set PTL_StartDate_Dt = Convert(Date, PTL_StartDate, 101 )
Where CHARINDEX('/', PTL_StartDate) > 0I get this error msg:
Conversion failed when converting date and/or time from character string.
That's the problem, you'll going to get those errors. Like I said before, you have both ddMMyyyy and MMddyyyy formats. If your server is using ddMMyyyy format and one of your dates is 02192017 then you have a conversion error: there aren't 19 month's in the year.
We need some kind of logic. How do you determine if a string is in the ddMMyyyy or MMddyyyy format? If you can't, what should the 01062017 be? Also, what date does 42550 represent?
You'll not going to get about to do anything about "24 May 2" that just isn't a date.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 18, 2017 at 11:51 am
SQLPirate - Wednesday, October 18, 2017 11:41 AMGrassHopper - Wednesday, October 18, 2017 11:30 AMSQLPirate - Wednesday, October 18, 2017 11:27 AMGrassHopper - Wednesday, October 18, 2017 11:15 AMI created a smalldatetime column (PTL_StartDate_Dt) and I tried running this update and did not work for me:PTL_StartDate is a varchar column with values
01/15/2017
11/22/2016Update [Promo]
set PTL_StartDate_Dt = Convert(Date, PTL_StartDate, 101 )
Where CHARINDEX('/', PTL_StartDate) > 0What happened when you tried the update?
I just quickly created a promo table with columns as you've stated them and the values and used your update and returned PTL_StartDate_Dt values of2017-01-15 00:00:00
2016-11-22 00:00:00I changed PTL_StartDate_Dt from SmallDateTime to Date.
Then, I tried the update again:
Update [Promo]
set PTL_StartDate_Dt = Convert(Date, PTL_StartDate, 101 )
Where CHARINDEX('/', PTL_StartDate) > 0I get this error msg:
Conversion failed when converting date and/or time from character string.Weird. And the only two values are the two you provided? I can throw both of those values directly into an update of PTL_StartDate_Dt without a CONVERT and it takes them just fine.
I did a direct update ...add isdate() and it worked:
Update [Promo]
set PTL_StartDate_Dt = PTL_StartDate
Where CHARINDEX('/', PTL_StartDate) > 0 and Isnumeric(Left(Ltrim(PTL_StartDate),1)) = 1 and ISDATE(ptl_startdate) = 1
Maybe there were some bad dates...it got 99% of them...i'm happy. Now i have to get the other date formats saved..
October 18, 2017 at 12:12 pm
This is what I have at the moment, but without the logic asked for, this is guess work. It doesn't rely on isdate, but has additional logic to deal with ddMMyyyy and MMddyyyy. If the conversion fails it gives MMddyyy a ago (if you're in a MMddyyyy part of the world you'll need to change the logic). I also assume that a integer is date 0 + that integer.USE Sandbox;
GO
CREATE TABLE #dates (dateString varchar(10));
GO
INSERT INTO #dates
VALUES
('03/15/2017'),
('14.06.15'),
('27.01.2014'),
('24th May 2'),
('09.09.2015'),
('42550'),
('27.07.16');
GO
SELECT dateString,
CASE WHEN dateString NOT LIKE '%[^0-9]%' THEN DATEADD(DAY, CONVERT(int,dateString),0)
WHEN TRY_CONVERT(date,dateString) IS NOT NULL THEN CONVERT(date, dateString)
ELSE TRY_CONVERT(date,SUBSTRING(dateString,4,3) + LEFT(dateString,3) + RIGHT(dateString, LEN(dateString) - 6)) END AS ConvDate
FROM #dates;
GO
DROP TABLE #dates;
Personally, however, like I said before, I wouldn't trust this data. Unless you can be sure that the date is in the format you expect, it's not going to give you correct dates (like i said before, you might have an entry of 06.01.2017 but is that May or January? You have no way of knowing). The fact that you have mixed data in there means that the source needs to be fixed. The issue is there, not at SQL Server's. Whereever you are gettingthe data from, I would speak to them.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 18, 2017 at 12:54 pm
Thom A - Wednesday, October 18, 2017 12:12 PMThis is what I have at the moment, but without the logic asked for, this is guess work. It doesn't rely on isdate, but has additional logic to deal with ddMMyyyy and MMddyyyy. If the conversion fails it gives MMddyyy a ago (if you're in a MMddyyyy part of the world you'll need to change the logic). I also assume that a integer is date 0 + that integer.USE Sandbox;
GOCREATE TABLE #dates (dateString varchar(10));
GO
INSERT INTO #dates
VALUES
('03/15/2017'),
('14.06.15'),
('27.01.2014'),
('24th May 2'),
('09.09.2015'),
('42550'),
('27.07.16');
GOSELECT dateString,
CASE WHEN dateString NOT LIKE '%[^0-9]%' THEN DATEADD(DAY, CONVERT(int,dateString),0)
WHEN TRY_CONVERT(date,dateString) IS NOT NULL THEN CONVERT(date, dateString)
ELSE TRY_CONVERT(date,SUBSTRING(dateString,4,3) + LEFT(dateString,3) + RIGHT(dateString, LEN(dateString) - 6)) END AS ConvDate
FROM #dates;GO
DROP TABLE #dates;Personally, however, like I said before, I wouldn't trust this data. Unless you can be sure that the date is in the format you expect, it's not going to give you correct dates (like i said before, you might have an entry of 06.01.2017 but is that May or January? You have no way of knowing). The fact that you have mixed data in there means that the source needs to be fixed. The issue is there, not at SQL Server's. Whereever you are gettingthe data from, I would speak to them.
TRY_CONVERT is a built in SQL function ? I have SQL 2016 and I get this error msg:
'TRY_CONVERT' is not a recognized built-in function name.
October 18, 2017 at 1:04 pm
GrassHopper - Wednesday, October 18, 2017 12:54 PMTRY_CONVERT is a built in SQL function ? if so, what version? I'm using 2008, but I have access to SQL 2016 SQL studio. The db is a 2008 format.
TRY_CONVERT was introduced in SQL 2012
October 18, 2017 at 1:24 pm
You could also try testing for specific formatsCREATE TABLE #dates (dateString varchar(10));
GO
INSERT INTO #dates
VALUES
('03/15/2017'),
('04/18/17'),
('14.06.15'),
('27.01.2014'),
('24th May 2'),
('09.09.2015'),
('42550'),
('27.07.16');
GO
SELECT dateString
, ConvDate = CASE
-- Assume that 42550 is a SQL, 0 based date number
WHEN dateString NOT LIKE '%[^0-9]%' THEN DATEADD(DAY, CONVERT(int,dateString),0) -- 42550
-- The dates with a "." appear to be DD.MM.YY[YY] format
WHEN dateString LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' THEN CONVERT(date, dateString, 104) -- 27.01.2014
WHEN dateString LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]' THEN CONVERT(date, dateString, 4) -- 27.07.16
-- The dates with a "'" appear to be MM/DD/YY[YY] format
WHEN dateString LIKE '[0-1][0-9]/[0-3][0-9]/[0-9][0-9][0-9][0-9]' THEN CONVERT(date, dateString, 101) -- 03/15/2017
WHEN dateString LIKE '[0-1][0-9]/[0-3][0-9]/[0-9][0-9]' THEN CONVERT(date, dateString, 1) -- 04/18/17
-- Let SQL try to determine which format.
WHEN ISDATE(dateString) = 1 THEN CONVERT(date, dateString)
END
FROM #dates;
GO
DROP TABLE #dates;
October 18, 2017 at 1:38 pm
GrassHopper - Wednesday, October 18, 2017 8:22 AMI imported a csv file that has a column with dates. The column is a varchar type and it has different format dates(see below). I created a new colum as datetime. I want to update the new datetime column with the values in the varchar column. How can i do this?03/15/2017
14.06.15
27.01.2014
24th May 2
09.09.2015
42550
27.07.16
You need to be aware that you have truncated data. The second date can be converted using format code 2 or 4. The fourth date is missing the 3 last digits of the year. There's no universal start date, so in SQL Server is interpreted as 2016-07-01, while in Excel is interpreted as 2016-06-29.
You mention that "Source is what it is" and you can't change that. I hope that you can tell your users that their data might be completely wrong.
That said, here's some code:
SELECT *,
CASE WHEN dateString LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' THEN CONVERT(datetime, dateString, 101)
WHEN dateString LIKE '[0-9][0-9].[0-9][0-9].[0-9][0-9]' THEN CONVERT(datetime, dateString, 4)
WHEN dateString LIKE '[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]' THEN CONVERT(datetime, dateString, 104)
WHEN dateString NOT LIKE '%[^0-9]%' THEN CONVERT( datetime, CONVERT( int, dateString))
WHEN dateString LIKE '[0-9][0-9][a-z][a-z] [a-z][a-z][a-z] [0-2]' THEN TRY_CONVERT( datetime, STUFF(RIGHT( dateString, 5)+RIGHT(YEAR(GETDATE()),3), 5, 0, LEFT( dateString, 2) + ' '))
END,
CASE WHEN dateString LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' THEN CONVERT(datetime, dateString, 101)
WHEN dateString LIKE '[0-9][0-9].[0-9][0-9].[0-9][0-9]' THEN CONVERT(datetime, dateString, 2)
WHEN dateString LIKE '[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9][0-9]' THEN CONVERT(datetime, dateString, 104)
WHEN dateString NOT LIKE '%[^0-9]%' THEN CONVERT( datetime, CONVERT( int, dateString))
WHEN dateString LIKE '[0-9][0-9][a-z][a-z] [a-z][a-z][a-z] [0-2]' THEN TRY_CONVERT( datetime, STUFF(RIGHT( dateString, 5)+RIGHT(YEAR(GETDATE()),3), 5, 0, LEFT( dateString, 2) + ' '))
END
FROM #dates
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply