October 20, 2017 at 9:55 am
I have another hurtle... This date format: '15 Mar 2017'
I am getthing an error msg when trying to update the dates:
Conversion failed when converting date and/or time from character string.
Drop Table #Staging
Go
SELECT *
INTO #Staging
FROM XLSHdr_Promo_TescoUK
WHERE (F_PromoCostRetroEndDate LIKE '[0-3][1-9]%[A-Z][a-z][a-z]%[0-9]')
--52,374
Update XLSHdr_Promo_TescoUK
Set F_PromoCostRetroEndDate_Dt = CONVERT(date, #Staging.F_PromoCostRetroEndDate + ' 01:00:00:000', 13)
From #Staging
Where XLSHdr_Promo_TescoUK.ID = #Staging.ID
October 20, 2017 at 10:02 am
I don't think '15 Mar 2017' Is the problem. For example SELECT CONVERT(date, '17 Mar 2017'); works fine.
In your sample data, as I'm going to mention again, you had a date along the lines of '17 Mar 2'. That format is going to match your above syntax, but is in no way a date. If you change the LIKE to the following, does it work?F_PromoCostRetroEndDate LIKE '[0-3][1-9] [A-Z][a-z][a-z] 20[0-1][0-9]'
(this filters to dates between 2000 and 2019).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 20, 2017 at 10:05 am
GrassHopper - Friday, October 20, 2017 9:55 AMI have another hurtle... This date format: '15 Mar 2017'I am getthing an error msg when trying to update the dates:
Conversion failed when converting date and/or time from character string.
Drop Table #Staging
Go
SELECT *
INTO #Staging
FROM XLSHdr_Promo_TescoUK
WHERE (F_PromoCostRetroEndDate LIKE '[0-3][1-9]%[A-Z][a-z][a-z]%[0-9]')
--52,374Update XLSHdr_Promo_TescoUK
Set F_PromoCostRetroEndDate_Dt = CONVERT(date, #Staging.F_PromoCostRetroEndDate + ' 01:00:00:000', 13)
From #Staging
Where XLSHdr_Promo_TescoUK.ID = #Staging.ID
I ran this, SELECT CONVERT(DATETIME,'15 Mar 2017',113), and it converted fine. Are you sure that is the date your conversion choked on?
October 20, 2017 at 11:25 am
Lynn Pettis - Friday, October 20, 2017 10:05 AMGrassHopper - Friday, October 20, 2017 9:55 AMI have another hurtle... This date format: '15 Mar 2017'I am getthing an error msg when trying to update the dates:
Conversion failed when converting date and/or time from character string.
Drop Table #Staging
Go
SELECT *
INTO #Staging
FROM XLSHdr_Promo_TescoUK
WHERE (F_PromoCostRetroEndDate LIKE '[0-3][1-9]%[A-Z][a-z][a-z]%[0-9]')
--52,374Update XLSHdr_Promo_TescoUK
Set F_PromoCostRetroEndDate_Dt = CONVERT(date, #Staging.F_PromoCostRetroEndDate + ' 01:00:00:000', 13)
From #Staging
Where XLSHdr_Promo_TescoUK.ID = #Staging.IDI ran this, SELECT CONVERT(DATETIME,'15 Mar 2017',113), and it converted fine. Are you sure that is the date your conversion choked on?
I changed my criteria per Thom A 's recommendation (LIKE '[0-3][1-9]%[A-Z][a-z][a-z] 20[0-1][0-9]') and still getting error.
I did scroll thru the data that is updating..and found this below. I think that is the issue? Any recommendations?
13th March 2016
14th March 2017
18th February 2015
18th July 2017
19 Apr 2015
19th May 2015
28th April 2015
October 20, 2017 at 11:41 am
GrassHopper - Friday, October 20, 2017 11:25 AMI did scroll thru the data that is updating..and found this below. I think that is the issue? Any recommendations?13th March 2016
14th March 2017
18th February 2015
18th July 2017
19 Apr 2015
19th May 2015
28th April 2015
Those "th"s are an issue. You will need to filter them out.
October 20, 2017 at 11:52 am
DesNorton - Friday, October 20, 2017 11:41 AMGrassHopper - Friday, October 20, 2017 11:25 AMI did scroll thru the data that is updating..and found this below. I think that is the issue? Any recommendations?13th March 2016
14th March 2017
18th February 2015
18th July 2017
19 Apr 2015
19th May 2015
28th April 2015Those "th"s are an issue. You will need to filter them out.
Any way to remove the "th" on the fly so they get processed along with the others?
October 20, 2017 at 11:58 am
GrassHopper - Friday, October 20, 2017 11:52 AMDesNorton - Friday, October 20, 2017 11:41 AMGrassHopper - Friday, October 20, 2017 11:25 AMI did scroll thru the data that is updating..and found this below. I think that is the issue? Any recommendations?13th March 2016
14th March 2017
18th February 2015
18th July 2017
19 Apr 2015
19th May 2015
28th April 2015Those "th"s are an issue. You will need to filter them out.
Any way to remove the "th" on the fly so they get processed along with the others?
declare @datestring varchar(20) = '19th May 2015';
select convert(date, replace(@datestring, 'th', ''));
October 20, 2017 at 12:07 pm
My post isn't there. Look at this for an idea:
DECLARE @TestTable TABLE (tableid INT IDENTITY(1,1), DateStr VARCHAR(30));
INSERT INTO @TestTable([DateStr]) VALUES ('13th March 2016');
SELECT * FROM @TestTable AS [tt];
UPDATE @TestTable SET
DateStr = REPLACE([DateStr],'th','')
WHERE
[DateStr] LIKE '%[0-9]th%';
SELECT * FROM @TestTable AS [tt];
October 20, 2017 at 12:21 pm
I'm surprised you've said that string like '18th February 2015' are being returned with the LIKE statement (as they wouldn't).
DesNorton - Friday, October 20, 2017 11:58 AMdeclare @datestring varchar(20) = '19th May 2015';
select convert(date, replace(@datestring, 'th', ''));
The problem with this, Des, (edit and now Lynn who has posted the same) is that if the OP has data with "10th" they probably have data with "1st", "21st" and even "31st". That presents a problem as...
REPLACE(REPLACE(REPLACE(REPLACE('1st August 2017','st ',' '),'nd ',' '),'rd ',' '),'th ',' ')
Returns '1 Augu 2017'. You're probably going to need to do something more like...REPLACE(REPLACE(REPLACE(REPLACE('1st August 2017','1st','1'),'nd ',' '),'rd ',' '),'th ',' ')
I know I've been shot down about it before, but I'm going to say it again; Get the provider to fix their data... There's far far far too many permutations. I woulnd't be surprised if we start seeing examples of dates such as 'Friday, 20th October 2017' soon, or (even worse), something stupid like 'Thursday, 11st October 2017' (2017-10-11 was a Wednesday 😉 ).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 20, 2017 at 1:16 pm
Thom A - Friday, October 20, 2017 12:21 PMI'm surprised you've said that string like '18th February 2015' are being returned with the LIKE statement (as they wouldn't).DesNorton - Friday, October 20, 2017 11:58 AMdeclare @datestring varchar(20) = '19th May 2015';
select convert(date, replace(@datestring, 'th', ''));
The problem with this, Des, (edit and now Lynn who has posted the same) is that if the OP has data with "10th" they probably have data with "1st", "21st" and even "31st". That presents a problem as...
REPLACE(REPLACE(REPLACE(REPLACE('1st August 2017','st ',' '),'nd ',' '),'rd ',' '),'th ',' ')
Returns '1 Augu 2017'. You're probably going to need to do something more like...REPLACE(REPLACE(REPLACE(REPLACE('1st August 2017','1st','1'),'nd ',' '),'rd ',' '),'th ',' ')I know I've been shot down about it before, but I'm going to say it again; Get the provider to fix their data... There's far far far too many permutations. I woulnd't be surprised if we start seeing examples of dates such as 'Friday, 20th October 2017' soon, or (even worse), something stupid like 'Thursday, 11st October 2017' (2017-10-11 was a Wednesday 😉 ).
That worked, Thanks!
October 20, 2017 at 1:44 pm
Thom A - Friday, October 20, 2017 12:21 PMI'm surprised you've said that string like '18th February 2015' are being returned with the LIKE statement (as they wouldn't).DesNorton - Friday, October 20, 2017 11:58 AMdeclare @datestring varchar(20) = '19th May 2015';
select convert(date, replace(@datestring, 'th', ''));
The problem with this, Des, (edit and now Lynn who has posted the same) is that if the OP has data with "10th" they probably have data with "1st", "21st" and even "31st". That presents a problem as...
REPLACE(REPLACE(REPLACE(REPLACE('1st August 2017','st ',' '),'nd ',' '),'rd ',' '),'th ',' ')
Returns '1 Augu 2017'. You're probably going to need to do something more like...REPLACE(REPLACE(REPLACE(REPLACE('1st August 2017','1st','1'),'nd ',' '),'rd ',' '),'th ',' ')I know I've been shot down about it before, but I'm going to say it again; Get the provider to fix their data... There's far far far too many permutations. I woulnd't be surprised if we start seeing examples of dates such as 'Friday, 20th October 2017' soon, or (even worse), something stupid like 'Thursday, 11st October 2017' (2017-10-11 was a Wednesday 😉 ).
Agreed Thom.
That said, I was responding to the posted sample data. Also hoping that the Op would see the pattern and figure out how to apply it.
October 20, 2017 at 2:49 pm
Thom A - Friday, October 20, 2017 12:21 PMI'm surprised you've said that string like '18th February 2015' are being returned with the LIKE statement (as they wouldn't).DesNorton - Friday, October 20, 2017 11:58 AMdeclare @datestring varchar(20) = '19th May 2015';
select convert(date, replace(@datestring, 'th', ''));
The problem with this, Des, (edit and now Lynn who has posted the same) is that if the OP has data with "10th" they probably have data with "1st", "21st" and even "31st". That presents a problem as...
REPLACE(REPLACE(REPLACE(REPLACE('1st August 2017','st ',' '),'nd ',' '),'rd ',' '),'th ',' ')
Returns '1 Augu 2017'. You're probably going to need to do something more like...REPLACE(REPLACE(REPLACE(REPLACE('1st August 2017','1st','1'),'nd ',' '),'rd ',' '),'th ',' ')I know I've been shot down about it before, but I'm going to say it again; Get the provider to fix their data... There's far far far too many permutations. I woulnd't be surprised if we start seeing examples of dates such as 'Friday, 20th October 2017' soon, or (even worse), something stupid like 'Thursday, 11st October 2017' (2017-10-11 was a Wednesday 😉 ).
Thom, I wish it was that easy. The data I receive in excel cannot be fixed. It was manually entered in and there are many users entering the data into a template. We received the data from the client and have no control over their process. I extracted the data and inserted it into SQL. It what you said was an option, believe me...that would have been the first thing I did. That's why I said... "it is what it is..." I wasn't being sarcastic or trying to shoot you down.
October 20, 2017 at 2:55 pm
On another update, I am getting the same error msg and I can't find the needle in the haystack. I was thinking of doing an update on a portion of the data at a time. In other words, break up the update into 10 passes until it fails and then i can concentrate on a smaller set of data to look at for the problem. The total size is 130,571 records. It has an ID column that is unique, but not in serial order. How can I break this update up into 10 update passes?
i.e. - ID numbers:
117915
117916
117917
118300
118301
118302
118512
118513
SQL Code:
Update XLSHdr_Promo_TescoUK
Set PTL_EndDate_Dt = Case WHEN #Staging.PTL_EndDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9][0-9][0-9]' THEN CONVERT(date, #Staging.PTL_EndDate, 104) -- 27.01.2014
WHEN #Staging.PTL_EndDate LIKE '[0-3][0-9].[0-1][0-9].[0-9][0-9]' THEN CONVERT(date, #Staging.PTL_EndDate, 4) -- 27.07.16
END
From #Staging
Where XLSHdr_Promo_TescoUK.ID = #Staging.ID
October 20, 2017 at 3:06 pm
Dumb question, is this a one time import or will this be a recurring import?
October 20, 2017 at 3:19 pm
Lynn Pettis - Friday, October 20, 2017 3:06 PMDumb question, is this a one time import or will this be a recurring import?
The import will be done once a year.
Viewing 15 posts - 31 through 45 (of 50 total)
You must be logged in to reply to this topic. Login to reply