Solved

SQL Datetime Output Format

Posted on 2009-05-04
6
643 Views
Last Modified: 2012-05-06
I am trying to adjust an SQL query to output in a specific Datetime format. I am close but it needs to output in British Format: dd/mm/yyyy HH:MM:SS.
I have it except there are spaces where the "/" are. I need the / displayed in the output
 
Here is the code I am using
  CASE
      When vSF.ModifiedDate is Null then '""'
      Else ('"' + ltrim(convert(char(20), vSF.ModifiedDate,13)) + '"')
 END,

So I imagine I have to combine it with a formatting string of some sort?
0
Comment
Question by:raddermant
  • 3
  • 2
6 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Use :
CONVERT(char(10), ModifiedDate, 103) + ' ' + CONVERT(char(8), ModifiedDate, 108)
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Try:
CONVERT(NVARCHAR, vSF.ModifiedDate, 131)
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
So your code would look like this:
CASE
      When vSF.ModifiedDate is Null then '""'
      Else ('"' + CONVERT(char(10), vSF.ModifiedDate, 103) + ' ' + CONVERT(char(8), vSF.ModifiedDate, 108) + '"')
END,

Also, 14 has a 2 digits for the year and MMM for the month, which is not the same as yours: dd/mm/yyyy HH:MM:SS
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Nice idea, AC.  
0
 

Author Comment

by:raddermant
Comment Utility
The exact final soulution ended up as:

["Access Date"]=
CASE
                When vSF.ModifiedDate is Null then '""'
                Else ('"' + ltrim (CONVERT(char(10), vSF.ModifiedDate,103)) + ' ' + (CONVERT(char(8),vSF.ModifiedDate, 108))+ '"')
END,
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now