Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Convert and move date to next business day

Author  Topic 

gaktheknife
Starting Member

12 Posts

Posted - 2015-05-05 : 08:02:42
I have been tasked with creating a flat file. One of the fields that I need is a date field. I need to write a query that pulls the date of this field unless it is on a weekend or holiday. If it’s on a weekend or holiday then push it to the next business day. This date field is stored in my system as a Numeric field. I am trying to write it is a case statement using our holiday table and some weekend exclusion code but I am deep in the weeds and drowning. Any ideas?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-05-05 : 08:25:37
Without seeing the DDL for your holiday tables, and without knowing how to convert the numeric field that represents date to a date/datetime, it is hard to say. In general, to skip weekends, you would do something like this:
SELECT
DATEADD(
dd,
CASE WHEN DATEDIFF(dd,0,YourDateField)%7>4 THEN 7-DATEDIFF(dd,0,YourDateField)%7
ELSE 0 END,
YourDateField
) AS NextBusinessDay
FROM
YourTable;
Go to Top of Page

gaktheknife
Starting Member

12 Posts

Posted - 2015-05-05 : 09:44:45
Thanks. Here is what I have so far. I have to convert this date from numeric -> varchar -> date. I just cant get it to work.
SELECT
convert(varchar, convert(datetime,DATEADD(dd,CASE WHEN DATEDIFF(dd,0,q.LAST_EVENT_PROC_DT)%7>4 THEN 7-DATEDIFF(dd,0,q.LAST_EVENT_PROC_DT)%7
ELSE 0 END,q.LAST_EVENT_PROC_DT))) AS NextBusinessDay
FROM
COLLECTION_EVENT_QUEUE q;
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-05-05 : 11:58:54
Can you post some sample data?
Go to Top of Page

gaktheknife
Starting Member

12 Posts

Posted - 2015-05-06 : 08:49:18
I hope this helps...This is the column I am trying to query:

LAST_EVENT_PROC_DT (numeric(8,0),not null)
It holds a date formatted like: 20150506

This is the flat file output:
'' + '|' +
'' + '|' +
cast(q.NEXT_EVENT_PROC_DT as varchar) + '|' + -- Due Date
cast(q.LAST_EVENT_PROC_DT +12 as varchar) + '|' + -- Disco Date = Due date +15
CONVERT(VARCHAR(10),GETDATE() +15,112) + '|' + -- Agency Send Date todays date + 15

I need the disco date to be LAST_EVENT_PROC_DT + 12 unless that date falls on a weekend or holiday,
if so the disco date would be the next business day
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-05-06 : 09:35:31
Assuming 20150506 represents May 6, 2015, here are a few options (because I have not understood the business rule that you want to implement). You can copy the code below to an SSMS query window and run it to see what it does.

CREATE TABLE #tmp(LAST_EVENT_PROC_DT NUMERIC(8,0));
INSERT INTO #tmp VALUES (20150501),(20150502),(20150503),(20150504),(20150505);


SELECT
LAST_EVENT_PROC_DT,
DATENAME(weekday, CAST(LAST_EVENT_PROC_DT AS CHAR(8))) DayOfLAST_EVENT_PROC_DT,

-- move weekends to weekdays
DATEADD(
dd,
CASE WHEN DATEDIFF(dd,0,CAST(LAST_EVENT_PROC_DT AS CHAR(8)))%7 >4 THEN 7-DATEDIFF(dd,0,CAST(LAST_EVENT_PROC_DT AS CHAR(8)))%7
ELSE 0 END,
CAST(LAST_EVENT_PROC_DT AS CHAR(8))
) AS NextBusinessDay,

-- move weekends to weekdays and add 12 days
DATEADD(
dd,
CASE WHEN DATEDIFF(dd,0,CAST(LAST_EVENT_PROC_DT AS CHAR(8)))%7 >4 THEN 7-DATEDIFF(dd,0,CAST(LAST_EVENT_PROC_DT AS CHAR(8)))%7
ELSE 0 END + 12,
CAST(LAST_EVENT_PROC_DT AS CHAR(8))
) AS NextBusinessDayPlus12,

-- add 12 days and then move weekends to weekdays
DATEADD(
dd,
CASE WHEN DATEDIFF(dd,0,DATEADD(dd,12,CAST(LAST_EVENT_PROC_DT AS CHAR(8))))%7 >4 THEN 7-DATEDIFF(dd,0,DATEADD(dd, 12,CAST(LAST_EVENT_PROC_DT AS CHAR(8))))%7
ELSE 0 END,
DATEADD(dd,12,CAST(LAST_EVENT_PROC_DT AS CHAR(8)))
) AS Plus12NextBusinessDay

FROM
#tmp;

DROP TABLE #tmp;
If any of that gives you the correct date that you are looking for, and if you want to format that date to any specific format, use the CONVERT function. For example this will format today's date in the mm/dd/yyyy format:
SELECT CONVERT(varchar(32),GETDATE(),101);
Other available formats are listed here https://msdn.microsoft.com/en-us/library/ms187928.aspx

Alternatively, you can use the format function, described here: https://msdn.microsoft.com/en-us/library/hh213505.aspx?f=255&MSPPError=-2147217396
Go to Top of Page

gaktheknife
Starting Member

12 Posts

Posted - 2015-05-06 : 10:47:08
This chunk is exactly what I need. Thank you so much.
DATEADD(
dd,
CASE WHEN DATEDIFF(dd,0,CAST(LAST_EVENT_PROC_DT AS CHAR(8)))%7 >4 THEN 7-DATEDIFF(dd,0,CAST(LAST_EVENT_PROC_DT AS CHAR(8)))%7
ELSE 0 END + 12,
CAST(LAST_EVENT_PROC_DT AS CHAR(8))
) AS NextBusinessDayPlus12

Where would I put the convert in here to get the output to 101 mm/dd/yyyy format?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-05-06 : 11:12:31
Put it outside of the whole expression:
CONVERT(CHAR(10),
DATEADD(
dd,
CASE WHEN DATEDIFF(dd,0,CAST(LAST_EVENT_PROC_DT AS CHAR(8)))%7 >4 THEN 7-DATEDIFF(dd,0,CAST(LAST_EVENT_PROC_DT AS CHAR(8)))%7
ELSE 0 END + 12,
CAST(LAST_EVENT_PROC_DT AS CHAR(8))
) , 101) AS NextBusinessDayPlus12
Go to Top of Page

gaktheknife
Starting Member

12 Posts

Posted - 2015-05-06 : 11:28:14
Ohhhhh....Thanks again
Go to Top of Page
   

- Advertisement -