How to get weekending date in select query

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 2008 Forums

Transact-SQL (2008)

How to get weekending date in select query

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-07-02 : 12:03:51

I am planning on using the following: how can i also get weekending date? usually it is saturday of every week, is teh weekending date.

SELECT
COUNT(*)
FROM
tab_ccsnetocrdata
WHERE
type = 'RM'
AND created_dt >= DATEADD(WEEK, DATEDIFF(WEEK, 0, SYSDATETIME()) - 1, 0)
AND created_dt < DATEADD(WEEK, DATEDIFF(WEEK, 0, SYSDATETIME()), 0)

Thanks a lot for the helpful info.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-02 : 12:55:27

I would create a calendar table with days and include a column for the week ending date and join to that. You will probably find it very useful if you are doing date processing.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-02 : 12:58:59

Here's one I did earlier. Just add a column for the last day of the week and add the calculation

;with cte as
(
select dte = convert(datetime,'20080101')
union all
select dte = DATEADD(dd,1,dte) from cte where dte < '20201231'
)
insert dim_Calendar
(
dim_Calendar_id,
Rep_Date,
FirstDayOfMonth,
LastDayOfMonth,
Rep_Year,
Rep_Month,
Rep_WeekOfYear,
Rep_DayOfWeek
)
select dim_Calendar_id = convert(int,convert(varchar(8),dte,112))
, c_Date = dte
, FirstDayOfMonth = DATEADD(mm,datediff(mm,0,dte),0)
, LastDayOfMonth = DATEADD(mm,datediff(mm,0,dte)+1,0)-1
, c_Year = YEAR(dte)
, c_Month = MONTH(dte)
, c_WeekOfYear = DATEPART(ww,dte)
, c_DayOfWeek = DATEPART(dw,dte)
from cte
option (maxrecursion 0)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-07-02 : 13:20:12

Hello Nigel,

Based on the system date is it not possible to gind out the weekending date?

I would like to create a job to run weekly once and will update the table, with the weekending of date, i am planning on running some reports which must show teh weekending of date column, that way the recipients of teh report can see the progress, week by week.

Thanks a lot for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-02 : 20:07:44

quote:
Originally posted by cplusplus

Hello Nigel,

Based on the system date is it not possible to gind out the weekending date?

I would like to create a job to run weekly once and will update the table, with the weekending of date, i am planning on running some reports which must show teh weekending of date column, that way the recipients of teh report can see the progress, week by week.

Thanks a lot for the helpful info.

you can get that

to get current week ending date use

DATEADD(wk,DATEDIFF(wk,0,GETDATE())+1,0)-1

and to get prev weekending date use

DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)-1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources