formatted date tin a view that stays a date datatype

Question

I am converting some Oracle views to TSQL. In Oracle, there are dates in a view that are formatted like:

TO_DATE (TO_CHAR (sysdate, 'mm/dd/yyyy'), 'mm/dd/yyyy') as SDATE'

When you look at the DDL for this view, the SDATE column has a data type of DATE

Is there a way to format a date column in a view in TSQL, that returns as a date? I'm finding the only way to format a date in SQL Server is to convert it into VARCHAR. I tried in the view:

format(CONVERT(DateTime, getdate(), 101),'MM/dd/yyyy') SDATE

which returns as nvarchar(4000) and if I convert it back to a date, it loses it formatting. The problem arises with the calling program erroring while calling the view, because it is expecting a date datatype.

Thanks,
Paul

Answer 1

In SQL Server, if the datatype of a value is DATETIME, it can not have formatting. All that is there is an internal coding of number of days since Jan 1 of the year 1900. There is no way to have formatting in the value unless you convert it to a varchar type. Normally you would want to do this in the calling program.

Tom

Answer 2

Try this: cast( getdate( ) as date ).

Answer 3

Hi @Paul A ,

Please try this:

SELECT FORMAT (getdate(), 'MM/dd/yyyy') as SDATE  

Best regards
Niko