Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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