Solved

Sql Date format in a trigger

Posted on 2012-04-07
5
285 Views
Last Modified: 2012-05-01
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
Comment
Question by:infotron
5 Comments
 
LVL 17

Expert Comment

by:Anuroopsundd
ID: 37819398
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 37819463
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
 

Author Comment

by:infotron
ID: 37819878
Worked perfectly - thanks

SET PDA_Notes5 = CONVERT(varchar(10), DATEADD(day,7,t.Trans_Time), 101)
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 37819909
SET p.PDA_Notes5=convert(DATETIME,convert(varchar, DATEADD(day,7,t.Trans_Time), 101),101)

Regards,
nishant
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 37821578
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS Subscription jobs disabled, yet still running 4 50
SSAS Hierarchy with columns with folder names 10 19
SQL DATEADD 10 71
SQL - format decimal in a string 5 40
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question