Convert and move date to next business day

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;

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;

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-05-05 : 11:58:54

Can you post some sample data?

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

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

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?

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

gaktheknife
Starting Member

12 Posts

Posted - 2015-05-06 : 11:28:14

Ohhhhh....Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources