May 17, 2019 at 1:37 pm
Hi,
it is a long story, but I have a process that enters dates incorrectly in a table, that I can not change. It enters the dates with the slashes going the wrong way. that is 04/25/2019 is shown incorrectly as 04\25\2019.
So I would like to put this as a check in the column REPLACE(status_date,'\','/'), not sure is that a default or a constraint. but either way not sure how to do that, and have not been able to find exactly how to do this online.
any ideas would be really appreciated.
Thank you
May 17, 2019 at 1:44 pm
Neither defaults nor constraints will help here.
But an INSTEAD OF INSERT trigger could do the work. Have a look here for an example/walkthrough.
Incidentally, what is the datatype of the target column? I'm hoping it's not varchar() ... If it is, I will have a different suggestion.
May 17, 2019 at 1:56 pm
yes, it is a varchar(), and yes I know it should be a Date but I do not think I will be able to change it.
And I could use a trigger but would really rather sue a constraint if that is possible.
Would you know how to do that?
Thnak you
May 17, 2019 at 2:05 pm
Would you know how to do that?
No I don't. Constraints and checks are there to prevent bad things happening. They do not change data.
Have you considered leaving the column as it stands and adding a new computed date column which does the transformation work for you & gives you a column of the proper type as a bonus?
May 17, 2019 at 2:26 pm
Typically you'd use an AFTER trigger to correct the data. If you wanted to, you could also have the trigger verify whether the value is a valid date or not.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER table_name__TR_correct_status_date
ON dbo.table_name
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
IF TRIGGER_NESTLEVEL(@@PROCID, 'AFTER' , 'DML') = 1
BEGIN
UPDATE tn
SET status_date = REPLACE(i.status_date, '\', '/')
FROM inserted i
INNER JOIN dbo.table_name tn ON tn.$IDENTITY = i.$IDENTITY /*or tn.<key_col> = i.<key_col>*/
WHERE UPDATE(status_date) AND i.status_date LIKE '\'
END /*IF*/
/*end of trigger*/
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 17, 2019 at 3:25 pm
Okay, that does look good.
Thanks
May 17, 2019 at 3:56 pm
I like Scott's approach but honestly, I'd avoid the LIKE. If this changes, it would likely be quicker to just update all rows inserted.
May 17, 2019 at 5:34 pm
Oops, I left out one important line, to prevent run-away trigger recursion. In this case the UPDATE itself would prevent recursion, but it's safer to always check, in case other UPDATE(s) are added later. Therefore, I added this into the original code:
IF TRIGGER_NESTLEVEL(@@PROCID, 'AFTER' , 'DML') = 1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 17, 2019 at 5:47 pm
I'm dating myself here, but:
Bill or George! Anything but Sue!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 17, 2019 at 6:20 pm
Thanks again for everyone's help here
May 17, 2019 at 6:29 pm
sorry, I meant use
May 17, 2019 at 7:47 pm
I'm a moron. Was responding to Sue H in another thread and that was on my mind when I read Scott's post. I try to avoid triggers, but if users are entering this data and you have to accept it, the trigger makes sense for a quick REPLACE() and cleanup.
May 18, 2019 at 8:07 am
Rather than a trigger, you could add a calculated column to handle the replace and convert the result to an actual date at the same time, e.g.
Drop Table If Exists #Temp
Create Table #Temp
(
UserEnteredDate VarChar(10) Not Null,
ActualDate As Try_Convert(Date, Replace(UserEnteredDate, '\', '/'), 103) Persisted
)
Insert into #temp(UserEnteredDate)
values ('12/02/2019'),('12\02\2019'),('bob')
Select * from #temp
Replace the Try_Convert with a Convert if you want entering an invalid string to fail (I'm guessing not based on currently allowing the wrong type of slashes)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply