• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

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?
0
infotron
Asked:
infotron
1 Solution
 
Anthony PerkinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now