July 23, 2013 at 10:16 am
Hello –
I currently have an SSIS package running and the first part of it deletes all data in (Table A) but saves the rows with the PIT of Friday at 4:00 PM for reference reasons.
DELETE FROM dbo.BackLogLaborOld
WHERE (datename(dw,PIT)<>'Friday' or datepart(hh,PIT) <> 16)
Now they want to change the day and time to Saturday at 12:00 PM to save but don't want to have the script delete all data from the past Friday’s at 4:00 PM data.
So I want to save the old Friday data but going forward change to the new day and time.
Could anyone suggest how I would go about doing that? Is that possible?
Regards,
David
July 23, 2013 at 10:34 am
david.ostrander (7/23/2013)
Hello –I currently have an SSIS package running and the first part of it deletes all data in (Table A) but saves the rows with the PIT of Friday at 4:00 PM for reference reasons.
DELETE FROM dbo.BackLogLaborOld
WHERE (datename(dw,PIT)<>'Friday' or datepart(hh,PIT) <> 16)
Now they want to change the day and time to Saturday at 12:00 PM to save but don't want to have the script delete all data from the past Friday’s at 4:00 PM data.
So I want to save the old Friday data but going forward change to the new day and time.
Could anyone suggest how I would go about doing that? Is that possible?
Regards,
David
could you add to the WHERE statement to also check the date going forward?
say if today is the cutoff day, it might be something like this:
--DELETE
SELECT *
FROM dbo.BackLogLaborOld
WHERE
(DW < CONVERT(DATETIME,'2013-07-23')
AND (DATENAME(dw,PIT) <> 'Friday'
OR DATEPART(hh,PIT) <> 16))
OR
(DW >= CONVERT(DATETIME,'2013-07-23')
AND (DATENAME(dw,PIT) <> 'Saturday'
OR DATEPART(hh,PIT) <> 12))
edited to fix the goofy date and the datepart of PIT, thanks to lshanahan for pointing that out!
Lowell
July 24, 2013 at 6:17 am
2103? Lowell: You're ahead of your time. 🙂
Also, it should be DATEPART(hh,PIT) <> 12 in the second part of the WHERE condition.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
July 24, 2013 at 10:08 am
Thank you for the responses. I will try and post with my results when we try it out.
Regards,
David
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply