Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 653
  • Last Modified:

SQL Datetime Output Format

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
raddermant
Asked:
raddermant
  • 3
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
0
 
Anthony PerkinsCommented:
Use :
CONVERT(char(10), ModifiedDate, 103) + ' ' + CONVERT(char(8), ModifiedDate, 108)
0
 
Kevin CrossChief Technology OfficerCommented:
Try:
CONVERT(NVARCHAR, vSF.ModifiedDate, 131)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony PerkinsCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
Nice idea, AC.  
0
 
raddermantAuthor Commented:
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

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.

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