Date Function - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Date Function

Author

Topic

cnbhold
Starting Member

43 Posts

Posted - 2013-04-12 : 15:36:38

How would I create a function that would return a list 10 days from the current date minus the weekends? If the current date was 4/12/2013, I would need to list the following available dates

4/15/2013
4/16/2003
4/17/2003
4/18/2003
4/19/2003
4/22/2003
4/23/2003
4/24/2003
4/25/2003
4/16/2003

Angel

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-12 : 16:30:28

quote:
Originally posted by cnbhold

How would I create a function that would return a list 10 days from the current date minus the weekends? If the current date was 4/12/2013, I would need to list the following available dates

4/15/2013
4/16/2003
4/17/2003
4/18/2003
4/19/2003
4/22/2003
4/23/2003
4/24/2003
4/25/2003
4/16/2003

Angel

I am assuming that the year for rows 2-10 being 2003 is a typo; and also that the date for the last row being 16 is a typo. Under all those assumptions you can use something like this:

SELECT TOP (10)
	DATEADD(dd,DATEDIFF(dd,0,GETDATE())+N,0)
FROM

  (VALUES

     (1),(2),(3),(4),(5),(6),(7),
     (8),(9),(10),(11),(12),(13),(14)
  ) AS C(N)
WHERE 
	(DATEDIFF(dd,0,GETDATE())+N)%7 < 5
ORDER BY
	1;

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-12 : 16:34:25

Will this work?

WITH DayCte AS
    (
        SELECT
            CAST(SYSDATETIME() AS DATE) AS MyDate
        UNION ALL
        SELECT
            DATEADD(DAY, 1, MyDate)
        FROM
            DayCte    
        WHERE
			MyDate <= DATEADD(DAY, 15, SYSDATETIME())
    )
    
SELECT TOP 10 MyDate
FROM DayCte
WHERE DATEDIFF(DAY, 0, MyDate) % 7 NOT IN (5, 6)

cnbhold
Starting Member

43 Posts

Posted - 2013-04-12 : 23:16:30

Lamprey, here's what I have so far. The date 4/18/2013 should not be in the final results since it's the orginal date I'm needing to add 10 days minus the weekends to.

SELECT Schedule_DT
FROM Migration

Schedule_DT
-----------------------
2013-04-18 21:46:40.340

(1 row(s) affected)

DECLARE @Schedule_DT DATETIME = (SELECT Schedule_DT FROM Migration);

WITH DayCte AS
(
SELECT
CAST(@Schedule_DT AS DATE) AS MyDate
UNION ALL
SELECT
DATEADD(DAY, 1, MyDate)
FROM
DayCte
WHERE
MyDate <= DATEADD(DAY, 15, @Schedule_DT)
)

SELECT TOP 10 MyDate
FROM DayCte
WHERE DATEDIFF(DAY, 0, MyDate) % 7 NOT IN (5, 6)

MyDate
----------
2013-04-18
2013-04-19
2013-04-22
2013-04-23
2013-04-24
2013-04-25
2013-04-26
2013-04-29
2013-04-30
2013-05-01

(10 row(s) affected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-13 : 01:54:26

[code]
DECLARE @Schedule_DT DATETIME = (SELECT Schedule_DT FROM Migration);

SELECT TOP 10 [Date]
FROM dbo.CalendarTable(@Schedule_DT+1,@Schedule_DT + 15,1,0)
ORDER BY [Date]
[/code]

dbo.CalendarTable function is as given below

http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

cnbhold
Starting Member

43 Posts

Posted - 2013-04-13 : 12:29:44

That worked!! Thanks Visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-15 : 02:08:33

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources