Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
brubakerbr
Yak Posting Veteran
53 Posts |
 Posted - 2013-08-08 : 17:12:21
|
| I have a table that is imported from an Excel spreadsheet into a staging table. There is a column labeled [EndDate] that is the termination date for an employee. I recently noticed that I have a bad date that has started to show up for employees that have been rehired. the EndDate goe to '9999-12-31' I simply need a script that will remove this date as I process the new information every month. I currently have a scheduled job that runs on the first of the month so I figured the easiest way to do this would be to use an "Execute SQl Task" at the end of the Data Flow and remove the bad date. I tried this already with the following script. UPDATE [dbo].[HRIS_EEMaster]SET [EndDate] = NULLWHERE EndDate = '9999-12-31' But it didn't work. I may have the placement wrong.My SSIS flow is as such.ExecutleSQLTask (Truncate the Staging Table) -> DataFlowTask (Load the Staging Table) -> ExecutleSQLTask (Replace missing CostCenters in the Staging Table)Then I thought I could add another ExecutleSQLTask (Remove the Bad Date) using the above script. Hopefully someone has a better idea. Thank you in advance for any help. Brian D. BrubakerBusiness Intelligence AnalystViega LLC |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-09 : 02:13:03
|
Since you are using SQL Server 2012, you have a new function named TRY_CONVERT.If the data cannot be converted the way you want, the result is automatically NULL.UPDATE dbo.Table1 SET EndDate = TRY_CONVERT(DATE, EndDate)If you want to FORCE a format too, useUPDATE dbo.Table1 SET EndDate = TRY_CONVERT(DATE, EndDate, 120) -- The 120 is the same value as using the ordinary CONVERT function.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-09 : 06:11:24
|
| Your update statement looks fine. WHats was the issue you faced? did you get some error in the package? or is it that package was success and update never happened? is there some trigger in the table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-09 : 07:01:18
|
What datatype is EndDate column?Try to use compact ISO date format instead.UPDATE dbo.HRIS_EEMasterSET EndDate = NULLWHERE EndDate = '99991231';
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
brubakerbr
Yak Posting Veteran
53 Posts |
Posted - 2013-08-09 : 09:36:44
|
| @SwePeso, I will try the TRY_CONVERT and see if that works.@visakh16, there was no error, it simply did not remove the date that I had. In looking at the script again while typing the response I noticed that the package that I added this to is the staging package, however, the script that ai wrote cleans the master data. I think I need to change the UPDATE statement to direct to [Staging].[HRIS_EEMaster].It looks like I was cleaning the master data only to reload the dirty data in another job that populates the master from the staging.(cant believe I missed that)Thank you both for your help.Brian D. BrubakerBusiness Intelligence AnalystViega LLC |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-08-09 : 12:09:20
|
quote:
Originally posted by brubakerbr
UPDATE [dbo].[HRIS_EEMaster]SET [EndDate] IS NULLWHERE EndDate = '9999-12-31'
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-09 : 12:34:13
|
Lamprey, you cannot set values with IS null. Only checking.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-08-09 : 12:41:09
|
| Doh. reading too fast.. :) |
|
|
|
|
|
|
|
|
Remove Bad Date