MayoorPatel
asked on
Trigger to Format Date Column Apon Insert
Hi there we have an application which writes to the database but in the english date formate 12/07/2006 meaning 12th July 2006 we need it to write 2006/07/12 in ISO format. However we are unable to gain access to the code in the application and therefore will have to do the processing on SQL server.
I need a trigger which will apon insert replace the date inserted with the ISO format. Can Anyone help?
500pts.
Mayoor
I need a trigger which will apon insert replace the date inserted with the ISO format. Can Anyone help?
500pts.
Mayoor
ASKER
Angel the field is of type datetime however dates are being inserted in english format and we have a function called getmonthdate which extracts the month from that date as follows
BEGIN
DECLARE @monthdate varchar(40)
set @monthdate= cast(month(@signupdate) as varchar) + '-' + cast(Year(@signupdate) as varchar)
-- Return the result of the function
RETURN (@monthdate)
END
However if you apply this function to the english date it extracts the day and not the month, because of the format whihc is why we need to reformat the column, unless you have any better ideas?
BEGIN
DECLARE @monthdate varchar(40)
set @monthdate= cast(month(@signupdate) as varchar) + '-' + cast(Year(@signupdate) as varchar)
-- Return the result of the function
RETURN (@monthdate)
END
However if you apply this function to the english date it extracts the day and not the month, because of the format whihc is why we need to reformat the column, unless you have any better ideas?
>Angel the field is of type datetime
then the issue is ONLY display, not storing.
>and we have a function called getmonthdate which extracts the month from that date as follows
please try this:
select convert(varchar(7), signupdate, 120) from yourtable
other methods:
select datepart(year, signupdate), datepart(month, signupdate) from yourtable
then the issue is ONLY display, not storing.
>and we have a function called getmonthdate which extracts the month from that date as follows
please try this:
select convert(varchar(7), signupdate, 120) from yourtable
other methods:
select datepart(year, signupdate), datepart(month, signupdate) from yourtable
ASKER
HI there those functions you gavce me are still grabbing the wrong part of the date, they are grabbing the "day"
the dates are stored in our DB as
07/12/2006 - dd/mm/yyyy
So your functions are pulling out the 07 thinking its the month.
the dates are stored in our DB as
07/12/2006 - dd/mm/yyyy
So your functions are pulling out the 07 thinking its the month.
ASKER
Actually I think to make this easier I should just convert all the dates in the db to ISO format that way all the functions will work. Any ideas how to write a statement to do this?
Then:
>Angel the field is of type datetime
that MUST be wrong. please check again, because that is contradictory to
>the dates are stored in our DB as 07/12/2006 - dd/mm/yyyy
>Angel the field is of type datetime
that MUST be wrong. please check again, because that is contradictory to
>the dates are stored in our DB as 07/12/2006 - dd/mm/yyyy
ASKER
SignupDate is of type datetime - in the db
dates are stored as
07/12/2006 18:17:34
when you do
set @monthdate= cast(datepart(month, '2006-12-07')as varchar)
+ '-' + cast(datepart(year, '2006-12-07') as varchar)
it returns 07-2006
which is wrong
it shoud be 12-2006
dates are stored as
07/12/2006 18:17:34
when you do
set @monthdate= cast(datepart(month, '2006-12-07')as varchar)
+ '-' + cast(datepart(year, '2006-12-07') as varchar)
it returns 07-2006
which is wrong
it shoud be 12-2006
the following will return the correct value:
set @monthdate= cast(datepart(month, convert(datetime, '2006-12-07' , 120) )as varchar)
+ '-' + cast(datepart(year, convert( datetime, '2006-12-07', 120) ) as varchar)
set @monthdate= cast(datepart(month, convert(datetime, '2006-12-07' , 120) )as varchar)
+ '-' + cast(datepart(year, convert( datetime, '2006-12-07', 120) ) as varchar)
>dates are stored as
>07/12/2006 18:17:34
I repeat: they are DISPLAYED like that, but not STORED like that if the field is datetime.
>07/12/2006 18:17:34
I repeat: they are DISPLAYED like that, but not STORED like that if the field is datetime.
ASKER
ok so how can i run a script to convert the dates in my db to a valid format so that my functions dont have to do this conversion everytime?
>run a script to convert the dates in my db to a valid format
you don't need to convert anything in the db itself.
you only have to
1) ensure the client applications store the date values correctly, but applying explicit conversion (like shown) to a string value, or in the client code to have date types also
2) ensure that for display, you also apply a given format to avoid any confusion.
you don't need to convert anything in the db itself.
you only have to
1) ensure the client applications store the date values correctly, but applying explicit conversion (like shown) to a string value, or in the client code to have date types also
2) ensure that for display, you also apply a given format to avoid any confusion.
ASKER
Right we have serious problems here.
I now have this function
FUNCTION [dbo].[getMonthDate] (@signUpDate datetime)
RETURNS varchar(40)
AS
BEGIN
DECLARE @monthdate varchar(40)
--set @monthdate= cast(month(@signupdate) as varchar) + '-' + cast(Year(@signupdate) as varchar)
set @monthdate= cast(datepart(month, convert(datetime, @SignUpDate , 120) )as varchar)
+ '-' + cast(datepart(year, convert( datetime, @SignUpDate, 120) ) as varchar)
-- Return the result of the function
RETURN (@monthdate)
END
WHEN I DO THIS
SELECT dbo.GetMonthDate(SignUpdat e) from tblUsers
I am geting this
8-2006
8-2006
8-2006
8-2006
8-2006
The signupdate is 12/08/2006 00:00:00 for those fields. It is still grabbing the day and not the month after making your changes.
sorry :(
I now have this function
FUNCTION [dbo].[getMonthDate] (@signUpDate datetime)
RETURNS varchar(40)
AS
BEGIN
DECLARE @monthdate varchar(40)
--set @monthdate= cast(month(@signupdate) as varchar) + '-' + cast(Year(@signupdate) as varchar)
set @monthdate= cast(datepart(month, convert(datetime, @SignUpDate , 120) )as varchar)
+ '-' + cast(datepart(year, convert( datetime, @SignUpDate, 120) ) as varchar)
-- Return the result of the function
RETURN (@monthdate)
END
WHEN I DO THIS
SELECT dbo.GetMonthDate(SignUpdat
I am geting this
8-2006
8-2006
8-2006
8-2006
8-2006
The signupdate is 12/08/2006 00:00:00 for those fields. It is still grabbing the day and not the month after making your changes.
sorry :(
>The signupdate is 12/08/2006 00:00:00 for those fields.
can you query the following:
select convert(varchar(20), signupdate, 120) , dbo.GetMonthDate(SignUpdat e) from tblUsers
I am SURE that 12/08/2006 actually is shown and DD/MM/YYYY ...
if you don't believe me, you can check
1) the convert(varchar(20),signup date, 120) will return in formation YYYY-MM-DD
2) try to enter a date value for 20th of december, it will show as 20/12/2006
can you query the following:
select convert(varchar(20), signupdate, 120) , dbo.GetMonthDate(SignUpdat
I am SURE that 12/08/2006 actually is shown and DD/MM/YYYY ...
if you don't believe me, you can check
1) the convert(varchar(20),signup
2) try to enter a date value for 20th of december, it will show as 20/12/2006
ASKER
HI there results of query!
2006-08-12 00:00:00 8-2006
2006-08-12 00:00:00 8-2006
2006-08-12 00:00:00 8-2006
2006-08-12 00:00:00 8-2006
2006-08-12 00:00:00 8-2006
2006-08-12 00:00:00 8-2006
2006-08-12 00:00:00 8-2006
2006-08-12 00:00:00 8-2006
2006-08-12 00:00:00 8-2006
2006-08-12 00:00:00 8-2006
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
does the table have a primary key? I assume it is called PK:
create trigger trg_reconvert_date
on yourtable
for insert, update
as
update yourtable
set yourdatefield = convert(varchar(10), convert(datetime, i.yourfield, 103) , 111 )
from yourtable t
join inserted i
on i.PK = t.PK
where i.yourfield like '??/??/????'