| Author |
 Topic  |
|
|
cplusplus
Constraint Violating Yak Guru
483 Posts |
 Posted - 07/02/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/02/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/02/2012 : 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
Constraint Violating Yak Guru
483 Posts |
Posted - 07/02/2012 : 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
India
48012 Posts |
Posted - 07/02/2012 : 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/
|
|
|
| |
Topic |
|
|
|
How to get weekending date in select query
