asked on 8/3/2016

How to format datetime as mm/dd/yyyy

Need to format data in a view to display as mm/dd/yyyy instead of the default the datetime format of yyyy/mm/dd. Convert?

SELECT        [ZSOFU5080VA07 VBAK-VBELN], [PART NUMBER], [ZSOFU5080VA07 MARA-MFRNR], CAST([SALES ORDER DATE] AS DATETIME) AS [SALES ORDER DATE], DIVISION, 
                         [ZSOFU5080VA07 VBAK-AUART], QUANTITY, [ZSOFU5080VA07 VBAP-POSNR], PLANT, [CUST. PO NUMBER], [STORAGE LOCATION], [REQUESTED DELIVERY DATE], 
                         [PRICE EACH], [PROFIT CENTER], [ZSOFU5080VA07 VBAP-PRCTR], [TOTAL PRICE], [CUSTOMER NUMBER], COUNTRY, [CUSTOMER NAME], [CONTACT INFO], CITY, 
                         [ZIP CODE], STATE, ADDRESS, [ZSOFU5080VA07 MAKT-MATNR], DESCRIPTION, [ZSOFU5080VA07 VBRP-VBELN], [ZSOFU5080VA07 VBRP-POSNR], [QTY INVOICED], 
                         INVOICED, [TOTAL COST], MARGIN, [ZSOFU5080VA07 VBRP-FKLMG], [ZSOFU5080VA07 LIPS-VBELN], [DATE CREATED], [ESTIMATED DELIVERY DATE], 
                         [GOODS ISSUE DATE]
FROM            dbo.ZSOFUVA5SALES_table

Open in new window

Microsoft SQL Server Microsoft SQL Server 2005 Microsoft SQL Server 2008 SQL

Last Comment

zephyr_hex (Megan)

8/3/2016

Jim Horn

8/3/2016

A buffet of date formats is in Paul Maxwell's article SQL Server Date Styles (formats) using CONVERT()

>Need to format data in a view to display as mm/dd/yyyy instead of ... yyyy/mm/dd
For presentation purposes --> SELECT convert(varchar, GETDATE() ,101)

zephyr_hex (Megan)

8/3/2016

DECLARE @test DATE = '2016-08-02'
SELECT CONVERT(VARCHAR(10),@test, 101)

Open in new window

returns:
08/02/2016

Jim Horn

8/3/2016

(typo fix in my original post, after looking at zephyr_rex's comment)
101 instead of 1, as the 100 means include the first two characters that represent the century.

maximus1974

8/3/2016

ASKER

Yes, for presentation purposes, thank you. Using SELECT convert(varchar, GETDATE() ,1) , how does the statement know what field to format?

SOLUTION

Jim Horn

8/3/2016