Solved

Sql Date format in a trigger

Posted on 2012-04-07
5
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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
SQL DATEADD 10 88
Amazon RDS migrate to SQL Server 3 49
T-SQL: Need Group By to use "fuzzy logic"?? 3 51
MSSQL Convert Char to Date Time 5 35
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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