SQL Server 2014 :: DATEADD - Grouping By Week

Aug 25, 2015

We have bee using something like the following to get the Monday for the specified date.

However, we came across a scenario where this is not working

DECLARE @dt AS datetime
SET @dt = '2/19/2006'
SELECT DATEADD( ww, DATEDIFF( ww, 0, @dt),0)

It had seemed to be working as expected, but we recently came across a problem where The above is returning 2/20, not 2/13

View 9 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Backups To Different Folders Based On Week Day

Oct 25, 2014

I am trying to create a job that would backup a DB dynamically to a different folder based on the week day. So if it's Saturday, backup to folder Saturday.

I have this

DECLARE @BackupLoc nvarchar (100)
DECLARE @DayOfWeek nvarchar (100)
set @BackupLoc = N'D:Backup'
set @DayOfWeek = (SELECT DATENAME(dw,GETDATE()))
set @BackupLoc = @backuploc + @DayOfWeek

[Code] ....

But it's giving me permission errors, although the AGENT and MSSQL service accounts are members of the local admins group.

View 2 Replies View Related

Week Base Grouping

Aug 7, 2007

Hi

SQL Server 2000
Table_A, Col_1 datetime

I want a query to group the date on weeks.
Please advise how ?

Thanks
Jawad

View 3 Replies View Related

Grouping By Week In Stored Procedure

Mar 14, 2007

Hi all,
I am using the below statement to get some dates grouped by date, in my SP. 
SELECT TOP 100 PERCENT COUNT(dbo.test.CallDate) AS CallCount, year(dbo.test.CallDate) AS CallYear, datepart(wk, dbo.test.CallDate) AS [Week] FROM dbo.test LEFT OUTER JOIN dbo.view1 ON dbo.test.CallID = dbo.view1.CallID LEFT OUTER JOIN dbo.view2 ON dbo.test.CallID = dbo.view2.CallID WHERE (dbo.view1.[ACCOUNT ID] = @accountid OR (dbo.view2.[ACCOUNT ID] = @accountid AND (convert(varchar(10),dbo.test.CallDate,121) BETWEEN CONVERT(DATETIME, @StartDate, 102)AND CONVERT(DATETIME, @EndDate, 102)) GROUP BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate) ORDER BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate) i gave startdate as 1/1/2007 and endDate as 2/18/2007 i am getting the reuslt as count year week 42 2001 32 2 2001 39 1 2001 51 1 2002 17 1 2002 19 106 2002 21 183 2002 22 226 2002 23 ................................. ................................... 1208 2007 1 1292 2007 2 actually i should get only the last 2 rows. Can anyone  please point out, why i am getting the 2001, 2002 data? and how to fix that?Thanks

View 2 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

Grouping By Week, Friday 5pm To Friday 5pm

Oct 5, 2007



Hi,

I need to run report that shows activity for Paediatrics Consultants who starts their audit week on Fri 5pm and finish next Fri at 5pm.

Set datefirst would work Fri to Thur but not with hours. And does not always seem to work either.

I need to be able to see historical data and do not want people to enter dates (from to), will group it by wek, consultant

Any clues? Cheers

Petr

View 3 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

T-SQL (SS2K8) :: Get Week Numbers From Dates - Saturday Being Start Of Week

Sep 17, 2015

i have the following table I need to select dates grouping them by weeks, my week start is Saturday to Friday

CREATE TABLE weekdays
(
datevalue datetime NOT NULL
, numericvalue INT NOT NULL
);
INSERT INTO weekdays (datevalue, numericvalue) VALUES

[code]....

The output should look like this

weeknototalvalue
362015-09-01 00:00:00.000
362015-09-02 00:00:00.000
372015-09-07 00:00:00.000
372015-09-08 00:00:00.000
382015-09-12 00:00:00.000
382015-09-13 00:00:00.000
382015-09-14 00:00:00.000
392015-09-19 00:00:00.000

View 4 Replies View Related

Create A Yesterday, This Week, Last Week Calculated Member

May 23, 2007

Hi all,

I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.

At the moment, I'm doing this:




Code Snippet

create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]

select [Measures].members on 0,
non empty [DATE TIME].[Date].members on 1
from [MIA DW]
But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?

So I guess there's two questions:

1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)

2)How come the Yesterday member is not returned by the .members function?


Thanks

Greg

View 3 Replies View Related

How To Stop Week Hours To Continue To Next Week

Jan 29, 2014

I am adding week hrs using while loop , but it can continue next week hrs also.

I can get every week start hrs while update @tem1 table

employeeidreportdatereportatleftattoaccountwrhrn
12902014-01-29 00:00:00.00009:3019:1556001
12902014-01-28 00:00:00.00009:0018:4555802
12902014-01-27 00:00:00.00009:0018:455585583
12902014-01-25 00:00:00.00008:0010:0012004 -- week end
12902014-01-24 00:00:00.00009:1718:4554105
12902014-01-23 00:00:00.00009:1918:4654606
12902014-01-22 00:00:00.00009:1718:4754507
12902014-01-21 00:00:00.00009:1618:3552608
12902014-01-20 00:00:00.00009:1818:555435439

My loop statement

while(select MAX(wrh) from @tem1 where wrh = 0) < 1
begin
update @tem1
set wrh = (select toaccount from @tem1
where reportdate = (select min(reportdate) from @tem1 where wrh = 0))+(select max(wrh) from @tem1)
where wrh = (select max(wrh) from @tem1 where wrh = 0 )
and reportdate = (select min(reportdate) from @tem1 where wrh = 0)
end

this is the result while executing loop statement .

employeeidreportdatereportatleftatdehdrhwehwrh
129029 Jan 201409:3019:15008:0009:20024:00065:54
129028 Jan 201409:0018:45008:0009:18016:00056:34
129027 Jan 201409:0018:45008:0009:18008:0009:18
129025 Jan 201408:0010:00005:0002:00045:00047:16 -- week end
129024 Jan 201409:1718:45008:0009:01040:00045:16
129023 Jan 201409:1918:46008:0009:06032:00036:15
129022 Jan 201409:1718:47008:0009:05024:00027:09
129021 Jan 201409:1618:35008:0008:46016:00018:04
129020 Jan 201409:1818:55008:0009:03008:0009:03

How to update only that week hrs , don't continue next week...

View 4 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

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

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

My DateAdd() Problem In Sql Server 2000

May 20, 2006

hello all !!!
i have a table having fields
table _ project  1)Duration 2)Enddate (null by default) these 2 fields
now i want to set constraint that
Enddate should be equal to Duration values from the dat  User inputs in Duration field data.... .... Enddate = todays date + Duration (int)
i tried
1)DateAdd(days,project.Duration,Getdate())
2)DateAdd(dd,project.Duration,Getdate())
please help me !!!!
 
 
 
 

View 2 Replies View Related

SQL Server 2012 :: DateAdd From Max DateValue Column

Jan 8, 2014

I am trying to generate a @StartDate and @EndDate. The @StartDate would be the 1st of January and the year is dependent on the @EndDate.

The @EndDate would be
select max(DateValue) from dbo.testtableThe column DateValue in the testtable contains dates such as 2013-12-09, 2013-12-15.

What I am trying to accomplish is if the @EndDate is populated by select max(DateValue) from dbo.testtable returns '2013-12-15' as the max(DateValue). I want the @StartDate to '2013-01-01' because the year of the EndDate is 2013.

So something along the lines of
@StartDate = DateAdd(yy,?,@EndDate)

View 2 Replies View Related

Date Of First Day Of Week For Current Week

Jun 6, 2006

seriously now

View 1 Replies View Related

Convert Getdate To Week And Day Of Week

Aug 15, 2006

I have a query that run every day to update a summary table which has week number and day of week. what I currently do is delete all records from the summary table and then summarize all the data availabe from four tables adn then populate the table daily. I want to know if I can run the update query to run only for the week number and day of week depending on getdate. Can I do this?

Please help..

View 1 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

How To Get Starting Datetime(monday) Of The Week And Ending Datetime Of The Week(sunday)

May 2, 2007

hi friends,



how to get the date of the first/last day of the week in sql...

can any one help me





Cheers,

raj

View 5 Replies View Related

Query Or Grouping Problem (some Kind Of Parallel Grouping?)

Nov 26, 2007

I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.

I'm trying to get information like this in to a report:

WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc

I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?

There are 4 tables:

Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions

The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:

WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc


Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR

It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:

SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]

View 1 Replies View Related

Changing The Day Of Week In SQL Server 2000

Jul 23, 2005

Dear All,I have one field in my table which shows the day of the week. It isshowingSunday = 1Monday = 2Tuesday = 3But i want Monday is the first day. I know i can use DATEFIRST tochange it but it works in Query Analyzerbut when i come and see mytable it is showing the old settings. Is it possible i can change itpermanently. Any help in this regard will be higly appreciatedRegardsS

View 2 Replies View Related

SQL Server 2008 :: How To Generate Week Ranges

May 28, 2015

I need to generate the week ranges like this format :

Here from date and to date would be picked up from the table but just to make you understand i have hardcoded it but this is the real date which is falling inside the table.

Note : Week should be generated from Monday to Sunday within desired date range

View 9 Replies View Related

How To Get The Week Number Of A Specific Date In SQL Server 2005

Jan 10, 2008

Hi,


Can anybody tell me to How to get the week number of a specific Date in SQL Server 2005.

Like : say Date "03/01/2008" belongs to the 1st week of the year 2008, same way "21/02/2008" is 8th week of the year 2008.

So, the same way I need to know the week number of a given (variable) date.


I need to write / use a Function in SQL server which will return the Number (INT) (i.e. 1 or 8 or 10 etc...) against a specific (Parameter) date.

regards,

Saibal


Note : Please feel free to write me if my question is not clear.

View 5 Replies View Related

SQL Server 2012 :: Comparing Data For A Certain Day Of Week By Month For Every Year

Oct 21, 2014

I need to build a report that compares a count on a certain day of the week by month by year by stacks. That is,for first Monday in October 2012 against first Monday in October 2013 for stack DM1 against first Monday in October 2014 stack DM1, same for second Monday, first Tuesday, second Tuesday, ect. Attached is a sample dataset and what I want to achieve.

View 9 Replies View Related

SQL Server 2008 :: Return Fiscal Year Based On YYYY-week Format

Nov 6, 2015

I have a table where hours are being loaded in a weekly basis. The YearWeek is populated when the data is loaded. The value format of the Year Week is 2015-39, 2015-41, etc. I need to calculate the total hours per Fiscal Year.For example, week '2015-39' will be return FY15 and week '2015-41' will return FY16, and so on. By extracting the year, I can do a group by and have total hours for each year.

Currently, I have it working by splitting the value into year and week and then looping through each year and week, so I can assign the totals to the corresponding FY.select sum(hours) as total, yearweek from tablename group by yearweek...Then I loop through using C#.I can return the FY using an actual date,how to do it for year-week format for any given year.

select CASE
WHEN CAST(GETDATE() AS DATE) >
SMALLDATETIMEFROMPARTS(DATEPART(YEAR,GETDATE()),09,30,00,000)
THEN
DATEPART(YEAR,GETDATE()) + 1 ELSE DATEPART(YEAR,GETDATE())
END AS FY

View 9 Replies View Related

Dateadd

Oct 17, 2006

Just want to double check this.To add 30 days to the current date in a stored procedure using SQL Server should be this: DATEADD(day, 30, GETDATE())Right?Thanks,Zath

View 1 Replies View Related

Dateadd In RS

Mar 1, 2006

Hi

Hope someone can help.

I've got to produce a report that filters information dependent on the purchase date based on various parameters.

The parameters are:

@yearend (Datetime)
@months(int)

the script that I wrote in SQL is

select assetno from assets where purch_date < = dateadd(mm,-@months, @yearend)

but when I try to use this in the dataset it comes up with the following error:

"Application usess a value of the wrong type for the current operation"

Any help would be very much appreciated.

View 3 Replies View Related

Dateadd

Feb 6, 2006

Hi guys I am trying to convert this to date add because MS SqL and Jet SQL dont exactly speak the same language

=DateSerial(Year([FINALSUIT])4,Month([FINALSUIT]),Day([FINALSUIT]))

I am trying to calcuate the finalsuit date for every 2 years becasue that when employees have to get their gaming license renewed, so I'm trying to add the renewal year by 2 because they have to get it done every two years

View 2 Replies View Related

Dateadd()

Mar 22, 2007

If I am running a scheduled job at the first of the month and I want to run an update sequence for the previous month, i was essentially using this sql:

@startdate = dateadd(mm, -1, getdate())
@enddate = dateadd(dd, -1, getdate())

So, will the @enddate represent the last day in the previous month or will that not work?

The Yak Village Idiot

View 1 Replies View Related

Sum Iff With DateADD

Apr 17, 2008

What is the correct format for getting the last three months of data. in sql its DATEADD(mm-3,getdate()) but for some reason the report doesnt want to take this? what am i doing wrong?






Code Snippet

=IIF(Sum(iif(Fields!Database_Size_Datetime.Value >= DATEADD(mm, - 3, GETDATE()), Cdbl(Fields!MonthlyDBTotal.Value), 0.0))/ 3)





By the way, im trying to get the average over the last three months by doing this.

View 3 Replies View Related

Need Help With DATEADD

Sep 26, 2006



I am using Derived Column Transformation Task to format my data as I am converting my DTS to SSIS. One of the columns requires to use DATEADD where I add 1 month and substruct one day from the date that I get in the flat file.(note that I always hardcode the day to "01" regardless of what I get in my flat file)

The current script looks like this:

DTSDestination("PeriodEndDate") =
dateadd("d",-1,dateadd("m",1,cdate(mid(cstr(DTSSource("Col004")),1,4) & "-" & mid(cstr(DTSSource("Col004")),5,2) & "-" & "01")))

Does any one know how to convert it so it works in my derived column?

Thanks...

Maria

View 3 Replies View Related

Bug In Dateadd?

Nov 19, 2007

when I execute the following:

select dateadd(dd,92710,1/1/1753)

I get 2153-10-31 00:00:00.000

in managed code I get the right answer 11/1/2006. Between 1753 and 2007, there should have been 2007 - 1753 = 254 yrs x approx 365 = 92,710 days. Where is the year 2153 coming from?

View 1 Replies View Related

DATEADD - Add Weekdays Only

Sep 20, 2006

Hi, I have a problem with working out some dates. I have a query that has a start date field and a number of days field. I know i can create another field that could provide the return date (DATEADD function) by adding the number of days to the start date. However the problem I have is that i need to discount the weekends from the return date. For example if the start date was a wednesday and the number of days was 3 the datadd sum would give a return date of saturday when in reality it should be monday. I am not sure if i am making sense but if anyone out there has any ideas it would be more than welcome. Andrew

View 8 Replies View Related







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