Link to home
Start Free TrialLog in
Avatar of MayoorPatel
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

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

is the field in varchar data type? bad choice...
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 '??/??/????'
Avatar of MayoorPatel
MayoorPatel

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?
>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

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.
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

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
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)
>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.
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.

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(SignUpdate) 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 :(
>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(SignUpdate) 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),signupdate, 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
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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial