Cast Function to display only the month and day from a datetime

Question

SELECT 
    DateAdded, 
    Cast(DateAdded AS Date) AS DateAddedV1, 
    Cast(DateAdded AS Time) AS DateTime, 
    SELECT CAST(DateAdded AS Date(mm:dd) AS OrderDate
FROM Products

I am a beginner with SQL and I am trying to return the DateAdded column as OrderDate with only the month and the day but I cannot seem to get the syntax right. I appreciate anyone's assistance.

Solution 1

Use DatePart Function to extract day and month from Datetime type,

Select Datepart(Month,DateAdded) AS [Month], -- Month(Dateadded)
       Datepart(Day,DateAdded) as [Day], -- Day(Dateadded)
       ..
From   Products

Update: Only by using cast function you cannot extract Month and Day. If you want to keep month and day in same column

    SELECT CONVERT(VARCHAR(2), Month(DateAdded)) + ':'
           + CONVERT(VARCHAR(2), Day(DateAdded)) 
           .....

To get leading zero's use right function and to extract Time from DateAdded use Convert function with 108 value

SELECT right('0'+CONVERT(VARCHAR(2), Month(DateAdded)),2) + ':'
       + right('0'+CONVERT(VARCHAR(2), Day(DateAdded)) ,2) as [Mon:Day],
         convert(varchar(10),DateAdded,108) as [Time]
       .........

Solution 2

I think I'm working on this assignment this semester. This is what I ended up with since it HAD to be done using the CAST function:

SELECT DateAdded,
    CAST(DateAdded AS date) AS DateOnly,
    CAST(DateAdded AS time) AS TimeOnly,
    CAST(DateAdded AS varchar(6)) MonthDay
FROM Products;

This works because varchar always has the Month the Day exactly in the first 6 characters. All the Months are 3 Characters (Jan, Feb, Mar, Apr) and then there is either a space then a 2-digit day, or 2 spaces and a 1-digit day. Either way it always adds up to 6 characters.

Solution 3

Here you are asking about the visible format of date/time information.

• In MS SQL Server from SQ 2012 and later you can use a FORMAT() function. If you use a version that supports this function I recommend you use it. Keep in mind that the second parameter is CASE SENSITIVE
• In all versions you may use the CONVERT() function, which has this syntax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Note in paticular that style parameter (an integer) this is important if using CONVERT. A very useful reference for using either approach for display of date/time information is HERE and below is a tiny example:

SQL Fiddle demo

Query 1:

SELECT
      FORMAT(SYSDATETIME(), 'MMM dd yyyy')  
    , CONVERT(VARCHAR(11), SYSDATETIME(), 100) -- note length set to (11)

Results:

|-------------|-------------|
| Oct 11 2015 | Oct 11 2015 |

Comments

[Jeff]

@ZackSpideyDavidson They probably want you to use CAST(DateAdded as Date) - I don't think there is a way to use the CAST function to remove the year. Also, using CAST as a formatting tool is stupid.

[Thomas Ashcraft]

FORMAT() is worst in terms of performance. You can read about it here: sqlperformance.com/2015/06/t-sql-queries/… I found that MONTH('Date') and YEAR('Date') methods are works faster than anything by orders of magnitude.