Solved

SQL Datetime Output Format

Posted on 2009-05-04
6
650 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
[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
  • 3
  • 2
6 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24297106
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24297107
Use :
CONVERT(char(10), ModifiedDate, 103) + ' ' + CONVERT(char(8), ModifiedDate, 108)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24297150
Try:
CONVERT(NVARCHAR, vSF.ModifiedDate, 131)
0
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 
LVL 75

Accepted Solution

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

Expert Comment

by:Kevin Cross
ID: 24297158
Nice idea, AC.  
0
 

Author Comment

by:raddermant
ID: 24630118
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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

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 post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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