How to return just the yyyy-mm-dd from the following SQL queries

Question

Below are the queries I created. Query #1 returns 2018-07-06 00:00:00.000 and query #2 returns 2018-07-31 23:59:59.997.

I can't figure out how to modify the queries so that they'll return the result in the yyyy-mm-dd format. Please advice.

Query #1 - get fifth business day of the month

SELECT 
    FifthWeekDay = DATEADD(dd, CASE 
                                  WHEN DATEDIFF(dd, -1, ca.FirstOfMonth) % 7 > 1 -- -1 is a Sunday
                                     THEN 7 
                                     ELSE 6 - DATEDIFF(dd, -1, ca.FirstOfMonth) % 7 -- -1 is a Sunday
                               END, ca.FirstOfMonth - 1)
FROM 
    (SELECT 
         DATEADD(mm, (SELECT DATEPART(YEAR, GETDATE())) * 12 - 22801 +
                     (SELECT DATEPART(M, GETDATE())), 0)) ca(FirstOfMonth)

Query #2 - get last business day of the month

SELECT 
    DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))
    - CASE DATENAME(dw, DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)))
         WHEN 'SUNDAY' THEN 2
         WHEN 'SATURDAY' THEN 1
         ELSE 0
      END AS LastBusinessCurrentMonth

Which dbms qre you using? (Those queries are product specific.) — jarlh, Jul 03 '18 at 20:19

Ian-Fogelman · Answer 1 · 2018-07-03T21:24:20.913

You can subquery the results you already have and add a cast( as date):

SELECT CAST(LastBusinessCurrentMonth AS DATE) AS LastBusinessCurrentMonth FROM(
SELECT 
    DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))
    - CASE DATENAME(dw, DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)))
         WHEN 'SUNDAY' THEN 2
         WHEN 'SATURDAY' THEN 1
         ELSE 0
      END AS LastBusinessCurrentMonth) AS X

SELECT CAST(FIFTHWEEKDAY AS DATE) AS FIFTHWEEKDAY FROM(
SELECT 
    FifthWeekDay = DATEADD(dd, CASE 
                                  WHEN DATEDIFF(dd, -1, ca.FirstOfMonth) % 7 > 1 -- -1 is a Sunday
                                     THEN 7 
                                     ELSE 6 - DATEDIFF(dd, -1, ca.FirstOfMonth) % 7 -- -1 is a Sunday
                               END, ca.FirstOfMonth - 1)
FROM 
    (SELECT 
         DATEADD(mm, (SELECT DATEPART(YEAR, GETDATE())) * 12 - 22801 +
                     (SELECT DATEPART(M, GETDATE())), 0)) ca(FirstOfMonth)) AS X

Or you could declare a variable as Date and set the results of your queries to that variable :

DECLARE @DATEVAR DATE

SET @DATEVAR =(
SELECT 
    DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))
    - CASE DATENAME(dw, DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)))
         WHEN 'SUNDAY' THEN 2
         WHEN 'SATURDAY' THEN 1
         ELSE 0
      END AS LastBusinessCurrentMonth)

SELECT @DATEVAR AS LastBusinessCurrentMonth

SET @DATEVAR = (SELECT 
    DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))
    - CASE DATENAME(dw, DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)))
         WHEN 'SUNDAY' THEN 2
         WHEN 'SATURDAY' THEN 1
         ELSE 0
      END AS LastBusinessCurrentMonth)

SELECT @DATEVAR AS LastBusinessCurrentMonth

How to return just the yyyy-mm-dd from the following SQL queries

1 Answers1