Construct a date from parts in SQL Server 2008

Question

I am using SQL Server 2008 and trying to form a query that will use a CASEstatement with a date calculation to return one of two dates depending on what day of the month the calculation results in.

The goal is if the calculation (in query below) CURRENT_TIMESTAMP + LEAD_TIME results in a day between the 1st and 15th, then assign the 15th of that month. Otherwise, return the last day of that month.

For example, if CURRENT_TIMESTAMP + LEAD_TIME = 07/12/19, return 07/15/19. If that calculation = 07/16/19, return 07/31/19.

This seems like it would be possible using DATEFROMPARTS but I believe since I am using SQL Server 2008 that function is not defined (that is the error I am returning). Any ideas on a work around?

SQL:

 SELECT I.po_number, 
       I.po_item_number AS 'po_item',
       S.orderentry_date, 
       I.po_req_ship_date, 
       I.ex_factory_date, 
       I.del_indicator,
       H.po_type,
       H.vendor_no,
       CASE WHEN DATEPART(dd,(CURRENT_TIMESTAMP + MM.IAM_MAN_LEAD_TIME)) BETWEEN 1 AND 15 
            THEN DATEFROMPARTS(DATEPART(yyyy,(CURRENT_TIMESTAMP + MM.IAM_MAN_LEAD_TIME),mm,(CURRENT_TIMESTAMP + MM.IAM_MAN_LEAD_TIME),15)
            ELSE DATEFROMPARTS(DATEPART(yyyy,(CURRENT_TIMESTAMP + MM.IAM_MAN_LEAD_TIME),mm,(CURRENT_TIMESTAMP + MM.IAM_MAN_LEAD_TIME)+1,0))
            END  AS 'LT_CALC',
       H.po_created_by,
       I.comment


FROM   rbk_sap_user..vw_po_header H 
JOIN   rbk_sap_user..vw_po_item I ON H.po_number = I.po_number
JOIN   rbk_sap_user..vw_mm_material MM ON I.material = MM.material
JOIN   (SELECT order_no,
               orderentry_date 
        FROM   asagdwpdx_prod..SimoxOrder1 

        UNION ALL

        SELECT order_no,
               orderentry_date 
        FROM   asagdwpdx_prod..SimoxOrder2 

        UNION ALL 

        SELECT order_no,
               orderentry_date 
        FROM   asagdwpdx_prod..SimoxOrder3
         ) S ON S.order_no = H.ahag_number

WHERE   S.orderentry_date BETWEEN '01/31/2019' AND '02/13/2019'
AND     I.del_indicator <> 'L'
AND     H.po_type NOT IN ('02','06','10','UB')
AND     MM.business_segment_code NOT IN ('420','421','422','424')

score 0 · Answer 1 · answered Feb 20 '19 at 23:30

0

Try this

SELECT CAST(LEFT(CONVERT(varchar, CURRENT_TIMESTAMP + LEAD_TIME, 103 ),2) as int)

then

CASE WHEN CAST(LEFT(CONVERT(varchar, CURRENT_TIMESTAMP + LEAD_TIME, 103 ),2) as int) < 15 THEN 
  ....
ELSE
  ....
END

answered Feb 20 '19 at 23:30

Derviş Kayımbaşıoğlu

28,492
4
50
72

score 0 · Accepted Answer · answered Feb 21 '19 at 03:26

You can convert all the parts of the date to VARCHAR and string them together to yyyy-mm-dd format, and then CONVERT toDATETIME.

 SELECT 
 ...
    CASE WHEN DATEPART(dd,(CURRENT_TIMESTAMP + MM.IAM_MAN_LEAD_TIME)) BETWEEN 1 AND 15 
            THEN CONVERT(DATETIME, CONVERT(varchar, DATEPART(yyyy,(CURRENT_TIMESTAMP + MM.IAM_MAN_LEAD_TIME)))+ '-' + CONVERT(VARCHAR, DATEPART (mm,(CURRENT_TIMESTAMP + MM.IAM_MAN_LEAD_TIME)))  + '-' + CONVERT(VARCHAR, 15))
            ELSE DATEADD(DAY, -1, CONVERT(DATETIME, CONVERT(VARCHAR, DATEPART(yyyy,(CURRENT_TIMESTAMP + MM.IAM_MAN_LEAD_TIME)))+ '-' + CONVERT(VARCHAR, DATEPART(mm,CURRENT_TIMESTAMP + MM.IAM_MAN_LEAD_TIME)+ 1)  + '-' + CONVERT(VARCHAR, 1)))
            END  AS 'LT_CALC'
...

Note that in the ELSE statement, the logic to get the last day of the month is to go the first day of the following month, and then use the DATEADD function to subtract one day. The SQL Server function EOMONTH , introduced in SQL Server 2012, eliminates the need for that logic.

Construct a date from parts in SQL Server 2008

2 Answers2