CODEWITHSUNDEEP
sqlsql-server-2008
Noelle
Noelle

Reputation: 782

Format of Sql DateTime object

CAST(IH.InvoiceDate AS date) AS InvoiceDate

this returns 2012-03-16 is it possible to format this in the select statement to return 16/03/2012

I'm using Sql Server 2008 R2

Upvotes: 0

Views: 18164

Answers (5)

James Johnson
James Johnson

Reputation: 46057

To achieve that format you'll need to convert it to a VARCHAR and apply a format. In this case, format 103 will output the date in dd/mm/yyyy format:

CONVERT(VARCHAR, CAST(IH.InvoiceDate AS DATE), 103) AS InvoiceDate

Assuming that InvoiceDate is a DateTime type, you can get rid of the cast too:

CONVERT(VARCHAR, IH.InvoiceDate, 103) AS InvoiceDate

Upvotes: 3

Taryn
Taryn

Reputation: 247760

use style 103

convert(varchar(10), IH.InvoiceDate, 103) as InvoiceDate

Here are two useful links regarding formatting date & time:

how to format date & time

SQL Server Date Formats

Upvotes: 2

Alex K.
Alex K.

Reputation: 175846

You need to convert it to a string to get it displayed ad dd/mm/yyyy (no need for the interim cast);

convert(varchar(10), IH.InvoiceDate, 103) AS InvoiceDate

Upvotes: 3

Massimiliano Peluso
Massimiliano Peluso

Reputation: 26737

you have to use the convert function

declare @date datetime

set @date='2012-03-16 '

SELECT convert(varchar, @date, 103)

for further reference below you can find all the possible format for a date

100 – mon dd yyyy hh:mmAM (or PM)
    – Oct  2 2008 11:01AM          
101 – mm/dd/yyyy - 10/02/2008                  
102 – yyyy.mm.dd – 2008.10.02           
103 – dd/mm/yyyy
104 – dd.mm.yyyy
105 – dd-mm-yyyy
106 – dd mon yyyy
107 – mon dd, yyyy
108 – hh:mm:ss
109 – mon dd yyyy hh:mm:ss:mmmAM (or PM)
    – Oct  2 2008 11:02:44:013AM   
110 – mm-dd-yyyy
111 – yyyy/mm/dd
112 – yyyymmdd
113 – dd mon yyyy hh:mm:ss:mmm
    – 02 Oct 2008 11:02:07:577     
114 – hh:mm:ss:mmm(24h)
120 – yyyy-mm-dd hh:mm:ss(24h)
121 – yyyy-mm-dd hh:mm:ss.mmm
126 – yyyy-mm-ddThh:mm:ss.mmm
     – 2008-10-02T10:52:47.513

Upvotes: 7

Diego
Diego

Reputation: 18359

Use convert with style 103 for French/British format:

CONVERT(Date, IH.InvoiceDate, 103) AS InvoiceDate

See: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Upvotes: 3

Related Questions