Sql Date format in a trigger

The set command below is contained in a trigger

SET p.PDA_Notes5 = DATEADD(day,7,t.Trans_Time)
which returns Oct  5 2012  1:21PM

Is there a way to convert this to mm/dd/yyyy on the fly?
infotronAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Is there a way to convert this to mm/dd/yyyy on the fly?
That really depends on the data type PDA_Notes5.  If it is datetime you display the result with something like this:
SELECT CONVERT(varchar(10), PDA_Notes5, 101)

If on the other hand PDA_Notes5 is varchar you can do the UPDATE as follows:
SET PDA_Notes5 = CONVERT(varchar(10), DATEADD(day,7,t.Trans_Time), 101)
0
 
infotronAuthor Commented:
Worked perfectly - thanks

SET PDA_Notes5 = CONVERT(varchar(10), DATEADD(day,7,t.Trans_Time), 101)
0
 
nishant joshiTechnology Development ConsultantCommented:
SET p.PDA_Notes5=convert(DATETIME,convert(varchar, DATEADD(day,7,t.Trans_Time), 101),101)

Regards,
nishant
0
 
Olaf DoschkeSoftware DeveloperCommented:
Indeed you can already convert to the wanted string format in SQL Server, but I'd recommend to keep Datetime and make it a job of the UI to convert to a string.

What you see after SET p.PDA_Notes5 = DATEADD(day,7,t.Trans_Time) is just the default display conversion of datetime, but what is stored in p.PDA_Notes5 is a datetime value and how it is displayed is your choice at a client/application to which you return that data.

Converting to a string too early disables making further dateadd() or other computations with the datetime.

Bye, Olaf.
0
All Courses

From novice to tech pro — start learning today.