Transact SQL :: Select All The Days In Week Number X Including Last Year If Necessary

May 24, 2015

SQL express 2012

I am trying to case in the where part and having a syntax errors - this is what i am trying to do:

Select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)

declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime

[Code] ...

View 2 Replies


ADVERTISEMENT

Transact SQL :: Select All Days In Week Number X Including Last Year If Necessary

May 24, 2015

SQL express 2012. I am trying to case in the where part and having a syntax errors - This is what i am trying to do:

select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)

declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime

[Code] ....

View 2 Replies View Related

Transact SQL :: Total Number Of Days Between Two Dates By Year?

May 6, 2015

I am currently working on a T-Sql query(Sql server 2008) to calculate total no of days between date ranges by year

Table:

Start Date End Date
01/01/2013 04/30/2014
11/01/2014 05/31/2015
06/01/2015 12/31/2015

My expected result.

2013 - 365
2014 - 181
2015 - 365

Note:

Date range can span b/w  multiple years

Date ranges will not overlap

I just want the total number of days covered by the range for each year.

Is there any simple way to do this calculation.

View 9 Replies View Related

Get Date From Week Number In A Year

Feb 11, 2014

Can I get a date from a week number in a certain year, like I know it's week 7 in 2014 (I may know the month.)

View 7 Replies View Related

Transact SQL :: Date Calculation - 7 Days Prior To Specified Day Of Last Week

Sep 8, 2015

I need to run a select on Mondays to pull data for 7 days prior to the Thursday of last week; i.e. Friday - Thursday inclusive.  I'm sure this is simple, but I work with dates so infrequently that I need a refressher. 

View 7 Replies View Related

Transact SQL :: How To Generate Week Ranges For A Year In 2005

Mar 22, 2011

I have to insert YEAR   WEEKNUMBER   STARTDATE   ENDDATE values to a datatable (sayweekrange), if I pass 2011 as year.

Week range starts at 2011-03-28 to 2011-04-03 (because in my database 2010 last week range ends with 2011-03-27) like this I have to generate for 52 weeks.

I want to write a stored procedure, that takes only year as parameter. with this year I have to generate week ranges and insert into my table as shown above.

How can I do this ?

View 8 Replies View Related

Return Number Of Days By Year From Date Fields

Dec 3, 2013

I need to associate aggregate gross_revenue with calendar year, but do not have a date field that reflects payment dates, just contract periods a start_date and an end_date. The contract periods are typically 1 or 2 years and can start at any time I.e start_date 6/1/2012, end date 5/31/13. I think by finding the number of days that fall in each calendar year and storing in a temp table, I can create a simple formula to associate revenue to each year.

View 2 Replies View Related

Transact SQL :: Display All Days Of A Given Month And Year

Oct 17, 2015

I need a simple query to display all the days of a given month and year

View 2 Replies View Related

SQL Server 2008 :: How To Calculate Number Of Days Since Start Of Fiscal Year

Jul 29, 2015

Our fiscal year starts on July 1st. Each month they call a period - so July is period 1, August is period 2, etc.

They are wanting a report that pulls numbers for a given period. There are parameters for them to select the fiscal year and the fiscal period, and then it calculates the numbers for that period. That part works fine.

Now they want me to do some calculations, and one of them is to divide one of the numbers by the # of days since the fiscal year. So if they choose July, it would be 31 days. If they choose August, it would be 61 days, etc. How can I set this up to calculate the number of days when they really aren't entering a start date, it's just a fiscal year and period.

Is there a way to calculate a date field that is 07/01/xxxx where xxxx is the fiscal year they chose? Also a way to calculate a date field that would be the last date of the month for the fiscal period and year they chose?

I suppose I could add 2 other parameters where they enter the start of the current fiscal year, and the last day of the period they're running it for, and use a datediff to calculate that. Just seems kind of redundant.

View 8 Replies View Related

Transact SQL :: Get Week Number From DateTime Field?

Aug 21, 2015

Any easier way to do what I am trying, without having a table with all the dates and week numbers.

Scenario: Week number of a DateTime Field where the year does not start on January 01<sup>st</sup> but April 1<sup>st</sup> to 31 Mach.

Issue: A week always starts on a Monday so if the 1<sup>st</sup> is on Tuesday, the first week is Tuesday – Sunday, if the 1<sup>st</sup> April is on Friday, the 1<sup>st</sup> week is Friday – Sunday and 7 day periods from there.

CREATE
TABLE [dbo].[DailyCanx](     
[ID] [int] IDENTITY(1,1)
NOT NULL,     
[DateCancelled] [datetime] NULL
)
ON [PRIMARY]

[code]....

I could create a table with all the start date and end dates of all the week numbers but I think there must be a better way. using SQL Server 2008 R2.

View 5 Replies View Related

Power Pivot :: Cumulative Total For Current Week Days And Previous Week

Nov 30, 2015

We are trying to compare our current calendar week (based on Monday being the first day of the week) with the previous calendar week. 

I'm trying to produce a line chart with 2 axis:

- x axis; the day of the week (Mon, Tues, Wed etc - it is fine for this to be a # rather than text e.g. 1 = Mon, 2 = Tues etc)
- y axis; the cumulative number of orders 

The chart needs two series:

Previous Week. The running count of orders placed that week. 
Current Week. The running count of orders placed this week. 

Obviously in such a chart the 'Current Week' series is going not going to have values along the whole axis until the end of the week. This is expected and the aim of the chart is to see the current week compares against the previous week for the same day. 

I have two tables:

Orders TableCalendar Table

The calendar table's main date column is [calDate] and there are columns for the usual [calWeekNum], [calMonth] etc. 

My measure for counting orders is simply; # Orders: = countrows[orders].

How do I take this measure and then work out my two series. I have tried numerous things such as adapting TOTALMTD(), following articles such as these:

- [URL] ...
- [URL] ...

But I have had no luck. The standard cumulative formulas do work e.g. if I wanted a MTD or YTD table I would be ok, it's just adjusting to a WTD that is causing me big issues.

View 3 Replies View Related

Transact SQL :: Number Of Days Between A Date And Its Month End

Sep 23, 2015

I have a column which stores a set of dates. I want to tell how many days left of a date till it’s month end. It should be noted that month ends are taken from the date series, not a calendar month end.
 
Something like below,
 
DateTD       Days left
2009-01-05  14
2009-01-06      13
2009-01-07      12
2009-01-08      11
2009-01-09      10
2009-01-12       9
2009-01-13       8
2009-01-14       7
2009-01-15       6
2009-01-16       5
2009-01-19       4
2009-01-20       3
2009-01-21       2
2009-01-22       1
2009-01-23       0
2009-02-02       /
2009-02-03       /

View 28 Replies View Related

Transact SQL :: User Defined Number Of Days

May 8, 2015

I am new to sql server query. I am trying to write a query for an application and I want user to enter number of over due days for payment.Below is my query and I am getting an error: An expression of non-boolean type specified in a context where a condition is expected, near 'Group'.

SELECT (P.FirstName+', '+P.LastName) As PA, Px.PRespDate,Px.DateFrom
 WHERE Px.Hide = 0 AND Px.Total <> Px.Payments AND Px.PRespDate IS NOT NULL AND PE.Hide = 0 AND PE.BillReady = 1 
AND DATEDIFF(DAY, Px.DateFrom, PRespDate)  LIKE 'Enter # of days |%% '
Group By (P.FirstName+', '+P.LastName), Px.PRespDate,Px.DateFrom

View 2 Replies View Related

Transact SQL :: Show N Number Of Column As Per No Of Days In Month

Aug 26, 2015

I want to show this kind of output

UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31

user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.

View 10 Replies View Related

Transact SQL :: Using Number Of Days As Stored Procedure Parameter

Aug 7, 2015

We have a stored procedure which currently accepts the following parameter: exec dbo.PurgeOldData <'yyyy-mm-dd'...We want to schedule this sp to run once a (first day of the month) going back to say today's date (or the date when this sp is called to run) minus 365 or purging 12 months data. Basically, the function should convert the today's date minus xxx number of days to yyyy-mm-dd so that the sp accept it as the correct parameter and purges the data successfully. So, if the sp is run on 2015-08-06, it will purge data going back to 2014-08-06 or earlier with the following parameter:

exec dbo.PurgeOldData <'2014-08-06'>

which means, all the data prior to the above date will be purged. Even the sp was run on 2015-08-06, the converted value of 2014-08-06' is entered as the sp parameter.

View 3 Replies View Related

How To Get Data Untill Last Week From The First Week Of The Year

Apr 29, 2008

In my reports I am extracting the data of number of people joined in all the weeks of the year. And in one of reports I have to extract the data of the number of people joined until the last week from the first week. I am trying out all the logics but nothing is working for me as such. Can any one help me with this issue??????

View 4 Replies View Related

ISO Year Week Day Of Week Function

Jan 18, 2006

Function F_ISO_YEAR_WEEK_DAY_OF_WEEK returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed. W01 represents the week of the year from W01 through W53, and D represents the day of the week with 1 = Monday through 7 = Sunday.

The first week of each year starts on the first Monday on or before January 4 of that year, so that the year begins from December 28 of the prior year through January 4 of the current year.

This code creates the function and demos it for the first day, first date+60, and first date+364 for each ISO week/year from 1990 to 2030.


drop function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK
GO
create function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK
(
@Datedatetime
)
returnsvarchar(10)
as
/*
Function F_ISO_YEAR_WEEK_DAY_OF_WEEK
returns the ISO 8601 Year Week Day of Week
in format YYYY-W01-D for the date passed.
*/
begin

declare @YearWeekDayOfWeekvarchar(10)

Select
--Format to form YYYY-W01-D
@YearWeekDayOfWeek =
convert(varchar(4),year(dateadd(dd,7,a.YearStart)))+'-W'+
right('00'+convert(varchar(2),(datediff(dd,a.YearStart,@Date)/7)+1),2) +
'-'+convert(varchar(1),(datediff(dd,a.YearStart,@Date)%7)+1)
from
(
select
YearStart =
-- Case finds start of year
case
whenNextYrStart <= @date
thenNextYrStart
whenCurrYrStart <= @date
thenCurrYrStart
elsePriorYrStart
end
from
(
select
-- First day of first week of prior year
PriorYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aaa.Jan4))/7)*7,-53690),
-- First day of first week of current year
CurrYrStart =
dateadd(dd,(datediff(dd,-53690,aaa.Jan4)/7)*7,-53690),
-- First day of first week of next year
NextYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aaa.Jan4))/7)*7,-53690)
from
(
select
--Find Jan 4 for the year of the input date
Jan4=
dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
) aaa
) aa
) a

return @YearWeekDayOfWeek

end
go


-- Execute function on first day, first day+60,
-- and first day+364 for years from 1990 to 2030.

select
DT= convert(varchar(10),DT,121),
YR_START_DT =
dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT),
YR_START_DT_60 =
dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+60),
YR_START_DT_365 =
dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+364)
from
(
select DT = getdate()union all
select DT = convert(datetime,'1990/01/01') union all
select DT = convert(datetime,'1990/12/31') union all
select DT = convert(datetime,'1991/12/30') union all
select DT = convert(datetime,'1993/01/04') union all
select DT = convert(datetime,'1994/01/03') union all
select DT = convert(datetime,'1995/01/02') union all
select DT = convert(datetime,'1996/01/01') union all
select DT = convert(datetime,'1996/12/30') union all
select DT = convert(datetime,'1997/12/29') union all
select DT = convert(datetime,'1999/01/04') union all
select DT = convert(datetime,'2000/01/03') union all
select DT = convert(datetime,'2001/01/01') union all
select DT = convert(datetime,'2001/12/31') union all
select DT = convert(datetime,'2002/12/30') union all
select DT = convert(datetime,'2003/12/29') union all
select DT = convert(datetime,'2005/01/03') union all
select DT = convert(datetime,'2006/01/02') union all
select DT = convert(datetime,'2007/01/01') union all
select DT = convert(datetime,'2007/12/31') union all
select DT = convert(datetime,'2008/12/29') union all
select DT = convert(datetime,'2010/01/04') union all
select DT = convert(datetime,'2011/01/03') union all
select DT = convert(datetime,'2012/01/02') union all
select DT = convert(datetime,'2012/12/31') union all
select DT = convert(datetime,'2013/12/30') union all
select DT = convert(datetime,'2014/12/29') union all
select DT = convert(datetime,'2016/01/04') union all
select DT = convert(datetime,'2017/01/02') union all
select DT = convert(datetime,'2018/01/01') union all
select DT = convert(datetime,'2018/12/31') union all
select DT = convert(datetime,'2019/12/30') union all
select DT = convert(datetime,'2021/01/04') union all
select DT = convert(datetime,'2022/01/03') union all
select DT = convert(datetime,'2023/01/02') union all
select DT = convert(datetime,'2024/01/01') union all
select DT = convert(datetime,'2024/12/30') union all
select DT = convert(datetime,'2025/12/29') union all
select DT = convert(datetime,'2027/01/04') union all
select DT = convert(datetime,'2028/01/03') union all
select DT = convert(datetime,'2029/01/01') union all
select DT = convert(datetime,'2029/12/31') union all
select DT = convert(datetime,'2030/12/30')
) a


Function Test Results:

DT YR_START_DT YR_START_DT_60 YR_START_DT_364
---------- ----------- -------------- ---------------
2006-01-18 2006-W03-3 2006-W11-7 2007-W03-3
1990-01-01 1990-W01-1 1990-W09-5 1991-W01-1
1990-12-31 1991-W01-1 1991-W09-5 1992-W01-1
1991-12-30 1992-W01-1 1992-W09-5 1992-W53-1
1993-01-04 1993-W01-1 1993-W09-5 1994-W01-1
1994-01-03 1994-W01-1 1994-W09-5 1995-W01-1
1995-01-02 1995-W01-1 1995-W09-5 1996-W01-1
1996-01-01 1996-W01-1 1996-W09-5 1997-W01-1
1996-12-30 1997-W01-1 1997-W09-5 1998-W01-1
1997-12-29 1998-W01-1 1998-W09-5 1998-W53-1
1999-01-04 1999-W01-1 1999-W09-5 2000-W01-1
2000-01-03 2000-W01-1 2000-W09-5 2001-W01-1
2001-01-01 2001-W01-1 2001-W09-5 2002-W01-1
2001-12-31 2002-W01-1 2002-W09-5 2003-W01-1
2002-12-30 2003-W01-1 2003-W09-5 2004-W01-1
2003-12-29 2004-W01-1 2004-W09-5 2004-W53-1
2005-01-03 2005-W01-1 2005-W09-5 2006-W01-1
2006-01-02 2006-W01-1 2006-W09-5 2007-W01-1
2007-01-01 2007-W01-1 2007-W09-5 2008-W01-1
2007-12-31 2008-W01-1 2008-W09-5 2009-W01-1
2008-12-29 2009-W01-1 2009-W09-5 2009-W53-1
2010-01-04 2010-W01-1 2010-W09-5 2011-W01-1
2011-01-03 2011-W01-1 2011-W09-5 2012-W01-1
2012-01-02 2012-W01-1 2012-W09-5 2013-W01-1
2012-12-31 2013-W01-1 2013-W09-5 2014-W01-1
2013-12-30 2014-W01-1 2014-W09-5 2015-W01-1
2014-12-29 2015-W01-1 2015-W09-5 2015-W53-1
2016-01-04 2016-W01-1 2016-W09-5 2017-W01-1
2017-01-02 2017-W01-1 2017-W09-5 2018-W01-1
2018-01-01 2018-W01-1 2018-W09-5 2019-W01-1
2018-12-31 2019-W01-1 2019-W09-5 2020-W01-1
2019-12-30 2020-W01-1 2020-W09-5 2020-W53-1
2021-01-04 2021-W01-1 2021-W09-5 2022-W01-1
2022-01-03 2022-W01-1 2022-W09-5 2023-W01-1
2023-01-02 2023-W01-1 2023-W09-5 2024-W01-1
2024-01-01 2024-W01-1 2024-W09-5 2025-W01-1
2024-12-30 2025-W01-1 2025-W09-5 2026-W01-1
2025-12-29 2026-W01-1 2026-W09-5 2026-W53-1
2027-01-04 2027-W01-1 2027-W09-5 2028-W01-1
2028-01-03 2028-W01-1 2028-W09-5 2029-W01-1
2029-01-01 2029-W01-1 2029-W09-5 2030-W01-1
2029-12-31 2030-W01-1 2030-W09-5 2031-W01-1
2030-12-30 2031-W01-1 2031-W09-5 2032-W01-1

(43 row(s) affected)




CODO ERGO SUM

View 2 Replies View Related

Transact SQL :: Select Between Days Record Of Every Month

Jun 29, 2015

I have 3 month of record in my table. if i pass 2 and 10, i need to select the record of between 2 and 10 days of record of every month. if i pass 10 and 20, it should select the record between 10 and 20 of every month. How to query for that?

View 8 Replies View Related

SQL Server 2008 :: Get Week Ending Date Given Week Number

Apr 9, 2013

How can I get Saturday's date given the week number?

This is my week number, SELECT DATEPART(WEEK, DATEADD(MONTH, +3, ApptDt2)). I need to get Saturday's date from this week.

View 9 Replies View Related

Transact SQL :: How To Select A Number Less Or Equal Than A Supplied Number

Jun 23, 2015

Got this query and I need the following result;

declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table 
select 4

[Code] ....

The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5

View 4 Replies View Related

Date Range Only Including Work Days -HELP!!

May 23, 2001

I've been fighting with this for a few days now, hopefully it's a simple answer.

I need to take a daterange between two dates and output the number of work days in that range.

I wrote this but it doesn't work. I know how to write a query that tells me if a date is a word day or not but not to actually determine a date range.

SELECT in_date,out_date,DATEDIFF((weekday, in_date, out_date) in (1,2,3,4,5)) AS no_of_days
from <table>
where ikey = 'whatever'

I am in desperate need of help as my deadline is coming up soon.

View 7 Replies View Related

Convert Week Number To Date Of 1st Day Of That Week...

Feb 2, 2008



I'm looking for a way to convert the [week number, year] back to the date for the 1st day of that week.

Example (assuming DATEFIRST has been set to 1) : Week 3, 2008 = January 14, 2008

Is there a SQL function that will do this?


Thanks,
Mike

View 11 Replies View Related

How Can I Retrieving Records By Past One Week,past Month, Past Quarter Of Year And Then Half Year.

Apr 30, 2007

Hi every one,
I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview.
How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.

View 4 Replies View Related

Transact SQL :: Start Week Of The Month For A Given Week

Jul 23, 2015

I need a Select sentence that return me the first week of the month for a given week.

For example If I have week number 12 (Begins 2015/03/16 and Ends 2015/03/22) I need that returns 9, I mean Week number 9 wich is the first week of march (having in mind @@DATEFIRST).

I only need give a week number of the year and then returns the week number of the first week of that month.

View 34 Replies View Related

Transact SQL :: Select From A Select Using Row Number With Left Join

Aug 20, 2015

The select command below will output one patient’s information in 1 row:

Patient id
Last name
First name
Address 1
OP Coverage Plan 1
OP Policy # 1
OP Coverage Plan 2

[code]...

This works great if there is at least one OP coverage.   There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table.   The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill.  The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.  

select src.pat_id, lname, fname, addr1,
max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,
max(case when rn = 1 then src.policy_id end) as OP_Policy1,

code]...

View 6 Replies View Related

Days Of The Week

Jan 24, 2006

Is there anyway to get SQL to convert a date to days of the week?

Example, I have a query where the date is "01/01/2006" but I would like it to display "Sunday". Thank you.

View 2 Replies View Related

Difference Between Days Of The Week

Aug 15, 2006

Hi! I am totally new to the forum and to the SQL.

Need your advice. My report will run on Friday automatically and I need to retrieve the data from previous Friday until Today’s Friday.

I have an example from another developer’s code, but this one gets the data on the previous day. Can you please help me to right the expression?

Here is an example ‘on the previous day’

CAST(CONVERT(CHAR(10), tbl1.closeddate,120) as datetime) = CAST(CONVERT(CHAR(10), dateadd(day, -1,getdate()), 120) as datetime)

This is what I did (assuming today is Friday)

(tbl1.closedDate BETWEEN CAST(CONVERT(CHAR(10), DATEADD(day, - 8, GETDATE()), 120) AS datetime) AND CAST(CONVERT(CHAR(10),
DATEADD(day, - 1, GETDATE()), 120) AS datetime))

I don't get any records back.

Please help.
Thanks a lot.

View 11 Replies View Related

Looping Through Days Of The Week.

Nov 20, 2007



SET @firstdayofweek = dateadd(day,(datepart(dw,@dtm1)*-1)+1,@dtm1)

--WHILE cast(@firstdayofweek AS DATETIME)AS INTEGER(100))

CREATE TABLE #tmp_table(


childid INTEGER null

,br BIT null

,di BIT null

,te BIT null

,type integer default 0

,DaysOfWeek INTEGER

)


INSERT #tmp_table

SELECT DISTINCT sa.childid

,CASE WHEN sha.br & @mybit>0 THEN 1 ELSE 0 END

,CASE WHEN sha.di & @mybit>0 THEN 1 ELSE 0 END

,CASE WHEN sha.te & @mybit>0 THEN 1 ELSE 0 END

,0

,1



FROM


sessionAttendance sa

,simplehoursassignment sha

,session s

,child c


View 3 Replies View Related

How Can I Search By Week No Of The Year?

Mar 31, 2006

How to define week of the year in sql? e.g. i want to search all the transcations in week 2, how can i do it?

View 2 Replies View Related

ISO Week Of Year Function

Jan 18, 2006

This function returns the ISO 8601 week of the year for the date passed. The first week of each year starts on the first Monday on or before January 4 of that year, so that the year begins from December 28 of the prior year through January 4 of the current year.

This code creates the function and demos it for the first day of each ISO week/year from 1990 to 2030.



drop function dbo.F_ISO_WEEK_OF_YEAR
go
create function dbo.F_ISO_WEEK_OF_YEAR
(
@Datedatetime
)
returnsint
as
/*
Function F_ISO_WEEK_OF_YEAR returns the
ISO 8601 week of the year for the date passed.
*/
begin

declare @WeekOfYearint

select
-- Compute week of year as (days since start of year/7)+1
-- Division by 7 gives whole weeks since start of year.
-- Adding 1 starts week number at 1, instead of zero.
@WeekOfYear =
(datediff(dd,
-- Case finds start of year
case
whenNextYrStart <= @date
thenNextYrStart
whenCurrYrStart <= @date
thenCurrYrStart
elsePriorYrStart
end,@date)/7)+1
from
(
select
-- First day of first week of prior year
PriorYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
-- First day of first week of current year
CurrYrStart =
dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
-- First day of first week of next year
NextYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
from
(
select
--Find Jan 4 for the year of the input date
Jan4=
dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
) aa
) a

return @WeekOfYear

end
go





-- Execute function on first day of first week of year from 1990 to 2030
select
DT,
ISO_WEEK_OF_YEAR =
dbo.F_ISO_WEEK_OF_YEAR(a.DT)
from
(
select DT = getdate()union all
select DT = convert(datetime,'1990/01/01') union all
select DT = convert(datetime,'1990/12/31') union all
select DT = convert(datetime,'1991/12/30') union all
select DT = convert(datetime,'1993/01/04') union all
select DT = convert(datetime,'1994/01/03') union all
select DT = convert(datetime,'1995/01/02') union all
select DT = convert(datetime,'1996/01/01') union all
select DT = convert(datetime,'1996/12/30') union all
select DT = convert(datetime,'1997/12/29') union all
select DT = convert(datetime,'1999/01/04') union all
select DT = convert(datetime,'2000/01/03') union all
select DT = convert(datetime,'2001/01/01') union all
select DT = convert(datetime,'2001/12/31') union all
select DT = convert(datetime,'2002/12/30') union all
select DT = convert(datetime,'2003/12/29') union all
select DT = convert(datetime,'2005/01/03') union all
select DT = convert(datetime,'2006/01/02') union all
select DT = convert(datetime,'2007/01/01') union all
select DT = convert(datetime,'2007/12/31') union all
select DT = convert(datetime,'2008/12/29') union all
select DT = convert(datetime,'2010/01/04') union all
select DT = convert(datetime,'2011/01/03') union all
select DT = convert(datetime,'2012/01/02') union all
select DT = convert(datetime,'2012/12/31') union all
select DT = convert(datetime,'2013/12/30') union all
select DT = convert(datetime,'2014/12/29') union all
select DT = convert(datetime,'2016/01/04') union all
select DT = convert(datetime,'2017/01/02') union all
select DT = convert(datetime,'2018/01/01') union all
select DT = convert(datetime,'2018/12/31') union all
select DT = convert(datetime,'2019/12/30') union all
select DT = convert(datetime,'2021/01/04') union all
select DT = convert(datetime,'2022/01/03') union all
select DT = convert(datetime,'2023/01/02') union all
select DT = convert(datetime,'2024/01/01') union all
select DT = convert(datetime,'2024/12/30') union all
select DT = convert(datetime,'2025/12/29') union all
select DT = convert(datetime,'2027/01/04') union all
select DT = convert(datetime,'2028/01/03') union all
select DT = convert(datetime,'2029/01/01') union all
select DT = convert(datetime,'2029/12/31') union all
select DT = convert(datetime,'2030/12/30')
) a








CODO ERGO SUM

View 12 Replies View Related

SQL Reporting Service Week Days Sum

Feb 4, 2008

hello please hlp

i have one report which list weekdays sum between two days

but the problems is that how i will calculate the week days sum

start date = ------- end date= ------


date | day | job| hours worked| total hours worked


-------------------------
week day sum hours workd=0


date | day | job| hours worked| total hours worked


-------------------------
week day sum hours workd=0


date | day | job| hours worked| total hours worked


-------------------------
week day sum hours workd=0


it should look like this ....


i can list out the week days between two dates but i dont know how to get sum of week day and show above format is there any programming methods are there to calculate the weeksays as field

please help me.....

thanks

View 5 Replies View Related

Find The Date If I Know The Week And Year

Aug 5, 2004

Hi Guys,
Can anyone please let me know How to find the first day of the week, if I know the weekno and the year.
For ex: If I know the week no is 22 and Year is 2003 then I should find a way to output the date. which is the first day (monday) of that week
for the above query the date will be eg:26/06/2003

I really appreciate your Help

Thanks:confused: Find the Date if I know the week and year

View 3 Replies View Related

Converting Year/week/day To Date

Mar 19, 2007

i have this SQL table with a year, week and day-of-week field, but no field containing the actual date

is there a way (e.g. in a view of the SQL table) to transform the year/week/day combination into a date string (e.g. format dd/mm/yyyy) ?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved