formatted date tin a view that stays a date datatype

Paul A 21 Reputation points
2022-11-14T17:31:41.657+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,596 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2022-11-15T04:15:37.857+00:00

    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


2 additional answers

Sort by: Most helpful
  1. Viorel 114.4K Reputation points
    2022-11-14T18:00:59.253+00:00

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


  2. NikoXu-msft 1,911 Reputation points
    2022-11-15T01:48:00.523+00:00

    Hi @Paul A ,

    Please try this:

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

    Best regards
    Niko