I have a query and am trying to just return the difference between two dates but not include weekends.
For instance, if I have 08/21/2006 - 08/28/2006, there are 6 weekdays.
I tried this, but I am getting 7 as a result.SELECT DATEDIFF(weekday, request_start_date, request_end_date) AS days_off, request_id
FROM request Any help would be greatly appreciated.
I have a weekly report that i want to display mon tues wed thur and fridays date from last week.
I know how to specifically pick one date:
=DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today))
that would set the parameter to monday. But how to do i make it select all the dates from monday-friday of the previous week?
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
I just discover the result for my query that had use the function dateDiff seems to be in-correct, no matter what's the date, the dateDiff always return a zero. Am I using it in-correctly?
select DateDiff(mm,11/1/2004, 12/31/2005)
SELECT component_id, component_description,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 2 THEN component_qty ELSE 0 END) AS mm1,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 1 THEN component_qty ELSE 0 END) AS mm2,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 0 THEN component_qty ELSE 0 END) AS mm3,sum(component_qty) as totalFROM view_jobcomponent WHERE date_complete between '10/1/2004' and '12/31/2004' GROUP BY component_id, component_descriptionorder by component_id, component_description
Hi GUYS
I am trying to use the datediff function to return a Y if difference is greater than 0 and a N if difference is less than or equal to 0
select if datediff(dd,maturity_date,log_changed) > 0 'Y','N')
and i am getting a syntax error.Can someone show me where i am getting it wrong.
In god we trust,everything else we test.
I've been trying to create a function that returns the difference, in months, between two dates. The DateDiff function (m) returns an integer, but I really need a decimal. I could return the days instead, but it would be difficult to figure out how the number of months from this, especially when the dates span multiple years and I need to adjust for leap year. Does anyone know of a resource that might have a solution for this?
Thanks,
Rob
Hi
I regularly use the T-SQL date functions to return the 1st of a particualr month.
e.g.
SELECT DATEADD(m,DATEDIFF(m,0,getdate()),0)
returns 2007-05-01 00:00:00.000
i.e the first of the current month at midnight.
However, when I try to use a similar expression as a derived column in SSIS it returns a completely different date.
DATEADD("month",DATEDIFF("month",(DT_DATE)0,GETDATE()),(DT_DATE)0)
returns 30/05/2007 00:00:00
Any ideas why and how I can obtain the first of a particualr month using SSIS derived column?
Dear all, I have the Following Query:SELECT DATEDIFF(day, GETDATE(), RECENT_RESERVATION)AS Expr1,RECENT_RESERVATION FROM EMP WHERE SUN=empName;when i run it inside the query analyzer, it returns two columns. but if run it inside The ASPX page it retuns only one column wich is RECENT_RESERVATION date.Note: i am using one methode that takes care of reading from SQL and assigning the result into an array, it works fine everywhere, but with this query it dosen't work. Any suggestions??
View 6 Replies View Relatedi have this FUNCTION
and the FUNCTION work OK
now how to add to this FUNCTION another mondy date ?
"last month last monday"
Code Snippet
CREATE FUNCTION [dbo].[Monday_List]( @arg_date datetime )
returns table
as
return
(
select convert(varchar(10), (dateadd(mm, datediff (mm, 0, @arg_date), 0)
+ (8 - datepart(dw, dateadd(mm, datediff (mm, 0,
@arg_date), 0))) % 7) + offset, 103)
as Date,
[Index],
Number
from
( select 1 as Number, 'First_Monday' as [Index], 0 as offset union all
select 2, 'Second_Monday', 7 union all
select 3, 'Third_Monday', 14 union all
select 4, 'Fourth_Monday', 21 union all
select 5, 'Fifth_Monday', 28
) x
where month(dateadd(mm, datediff (mm, 0, @arg_date), 0)
+ (8 - datepart(dw, dateadd(mm, datediff (mm, 0,
@arg_date), 0))) % 7)
= month((dateadd(mm, datediff (mm, 0, @arg_date), 0)
+ (8 - datepart(dw, dateadd(mm, datediff (mm, 0,
@arg_date), 0))) % 7) + offset)
)
this is the Output i get
select * from Monday_List('1/3/8')
Date Index Number
---------- ------------- -----------
03/03/2008 First_Monday 1
10/03/2008 Second_Monday 2
17/03/2008 Third_Monday 3
24/03/2008 Fourth_Monday 4
31/03/2008 Fifth_Monday 5
*/
now i need to add to the FUNCTION "last month last monday"
like this
select * from Monday_List('1/3/8')
Date Index Number
---------- ------------- -----------
04/02/2008 last monday 0 ---------------------- i need to add
03/03/2008 First_Monday 1
10/03/2008 Second_Monday 2
17/03/2008 Third_Monday 3
24/03/2008 Fourth_Monday 4
31/03/2008 Fifth_Monday 5
*/
------------------------------
TNX
I have a requirement to use DateDiff(Months,DateTime1,DateTime2) and this must return positive integer values.
Currently negative numbers are being returned because DateTime1 < DateTime2  or DateTime1 > DateTime2 .
The DateTime1 and  DateTime2  values are dynamic and either of them can be bigger than the other.
Any query solution so that always positive value is returned.
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 RelatedDoes anyone know how to write code to tell the amount of days between 2 dates excluding Saturday and Sunday.
Datediff will tell me the total amount of days and I guess I need some logic to look at the startdate and manipulate the total after the fact.
Any thoughts or solutions out there?
Hi All,
I am having some problems to calculate the basic work hours for a particular month.
Example is I chose for the month of November, I would like to calculate the number of working days (not weekends) and then multiply by 8 (assuming work hour is 8). The month also would be dynamically chosen from a list and not a fix attribute
Can this be done on reporting services?
Basically I am trying to port the same calculation from crystal report.
The crystal report code is as below:
(if DayOfWeek ({?From}) in 2 to 5 then
8
else if DayOfWeek ({?From}) = 6 then
7
else 0)
+(DateDiff ("ww",{?From} ,{?To}, crMonday)* 8) + (DateDiff ("ww",{?From} ,{?To}, crTuesday)* 8)
+ (DateDiff ("ww",{?From} ,{?To}, crWednesday)* 8) +
(DateDiff ("ww",{?From} ,{?To}, crThursday) * 8)+
(DateDiff ("ww", {?From}, {?To}, crFriday)*7 )
Thanks in advance for your help.
Regards,
Mohd Fadzli
Is there an easy way to find out what date was last friday to a given date? For example today is 20 Sep 2007, what would be the expression to find last friday to this date (14 Sep 2007) ?
View 9 Replies View RelatedHi all...I'm still trying to figure out a query related to the topic I posted yesterday about the table scan...got past that...it was smoke and mirrors...
Now...I have a table that looks like (essentially) this:
BegDate (smalldatetime)
EndDate (smalldatetime)
PortfolioID (int)
WklyPerPriceChg (float)
I am trying to come up with a query that returns a table that looks like this:
BegDate, EndDate, WklyPerPriceChg for PortfolioID1, WklyPerPriceChg for PortfolioID2, WklyPerPriceChg for PortfolioID3, WklyPerPriceChg for PortfolioID4
Here is my select:SELECT TOP 100 PERCENT
T.BegDate as StartDate,
T.EndDate as EndDate,
SP.WklyPerPriceChg as Sandp,
WR.WklyPerPriceChg as WeekRvw,
NA.WklyPerPriceChg as NewAm,
T.WklyPerPriceChg as T100
FROMdbo.WeeklyPortfolioIndex T (nolock) INNER JOIN
dbo.WeeklyPortfolioIndex WR (nolock) ON T.BegDate = WR.BegDate INNER JOIN
dbo.WeeklyPortfolioIndex NA (nolock) ON T.BegDate = NA.BegDate INNER JOIN
dbo.WeeklyPortfolioIndex SP (nolock) ON T.BegDate = SP.BegDate
WHERE((T.BegDate >= @FirstBegDate) AND
(T.EndDate <= @LastEndDate)) AND
SP.PortfolioID = 2 AND
WR.PortfolioID = 67 AND
NA.PortfolioID = 11 AND
T.PortfolioID = 90
ORDER BY T.BegDate DESC
I think you can disregard the date stuff that seems to be working.
What I am finding though, is that the self-join only returns rows for dates that have ALL FOUR of the target portfolioID's. What I want is a row for any day that has AT LEAST ONE of the portfolios, and would like NULL or zero returned in the columns for any portfolioID that does NOT have a row present in the table for that date(range).
I thought I would try a FULL OUTER join rather than the inner one (which I would expect to return an output row if ALL portfolio rows were present for that day)...but that returns more of a cartesian product type thang rather than what I am looking for.
I further thought I need something like a GROUP BY T.BegDate, for example, but then it complains that my select columns are not aggregates.
I know this should be fairly easy...I'm just missing the boat by mere inches, I think...
And missing by "mere inches" is still enough to leave me all wet ;)
Thoughts?
Thanks!
If I put this in crystal reports I can manipulate it ok, but actually translating it to a command isn't so easy for me. I'm terrible at cursors and need some help here, pretty pretty please.
I have a table...
CREATE TABLE [dbo].[StagePayment] (
[PaymentID] [uniqueidentifier] NOT NULL ,
[JobID] [uniqueidentifier] NOT NULL ,
[Paid] [bit] NOT NULL ,
[Sequence] [smallint] NOT NULL ,
[Amount] [decimal](10, 2) NOT NULL ,
[Activity] [varchar] (30) NULL ,
[ActivityID] [uniqueidentifier] NULL ,
[DueDate] [datetime] NULL ,
[InvoiceNumber] [varchar] (50) NULL ,
[DueNext] [bit] NOT NULL
) ON [PRIMARY]
GO
Simple example of the data I need to work with...
PayIDJobIDPaidSequenceAmountDueNext
609412110.000
6B1E 18110.000
297C 25110.000
E175 30114390.000
8CF9 30124390.000
916839114400.000
59F4 39126632.000
BCAF 5411417.500
59225402417.500
8DA0 68112988.000
76C5 68129958.000
0C13 68137566.000
F3F4 68142390.000
What I need to accomplish is flagging the DueNext field if the amount due has not been paid. I cannot rely on the Paid field as it's currently user controlled. I need to compare what's due with the total already paid (stored in another table).
So for instance,
Job #68 - the total amount due would be $22902.00
If the customer has paid $15000.00 then Sequence 3, PayID 0C13, should be flagged as DueNext.
Anyone have a very simple example of a running total cursor that I can reference or a better idea of how to work with this? I appreciate any and all suggestions!
Thanks!
abbra_doo
I want to make a function which returns the next Friday, given a day in the week. I can't find a solution which works. For example:
Input -> Output
4/22/08 -> 4/23/08
4/23/08 -> 4/23/08
4/24/08 -> 4/30/08
I can't find a solution which works. According to one website, this should do it:
DECLARE @Friday DATETIME
SET @Friday = '20000107'
SELECT DATEADD(day, ((DATEDIFF(day, @Friday, GETDATE()) / 7) * 7), @Friday)
'twas the friday before Christmas,
and all through the company,
everyone appeared to be gone,
except for lil' ol' me.
when I gander at Lotus Notes,
I can see that they are dialed in,
surely asleep on their couch,
but "available over IM".
To be there myself,
is all that I wish.
The highlight of the day
was a lunch of fried fish.
Back to the query analyzer,
dutifully I shall go,
just thought I would post,
to wish you all a merry ho ho...
Here is what I need to accomplishiif(len(sMemoText) > 15, left(sMemoText, 15) + '...', sMemoText)How to convert this into a case or anyother way to get this into aselect statement...Thanks MUCH!Jeff
View 3 Replies View RelatedI am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.
View 9 Replies View RelatedAttendee Registration URL: http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032290575&Culture=en-US
I'll be talking about database mirroring starting @ 9:30 AM Pacific Daylight Time.
Thanks,
Mark
I am writing an sql query as follow:SELECT DATEPART(dw, CALLSTARTTIME) AS dayFROM TABLEGROUP BY DATEPART(dw, CALLSTARTTIME)ORDER BY DATEPART(dw, CALLSTARTTIME)it returnsday====1234567my question is, how can I return monday, tuesday, wednesday, etc... insteand of 1-7?
View 1 Replies View RelatedHi,
Can any one help to get the date of second monday of october for the given year.
Ex: the input will be 10, 2006
and output should be 9, where 9 is the second monday date
hi need help
how can i put all the monday'S day next month in one table like this
so i can use it all years
date index number
---------------------------------------------------------
04/02/2008 first_monday 1
11/02/2008 second_monday 2
18/02/2007 third_monday 3
25/02/2008 fourth_monday 4
TNX
I'm using VB.Net and SQL Server. Here's the scenario I have that I can't figure out how to accomplish:
(btw, this is all manual - I'm NOT using an ASP.Net Calendar control) I want to provide a week's worth of calendar data automatically. Let's say I have all the calendar dates and times in a table. But - I only want to show the data from the current week.
So, no matter which day the end user loads the page (Monday, Tuesday, Saturday, etc), I want to be able to find the previous Sunday (or Monday?), and then provide data from the table for events that are happening from that Monday, through the next Sunday.
Finding the last Monday (or Sunday?) is the first problem, and then getting only data between that date and the following Sunday is the second problem - how to create the code and hw to create the sql statement - -
any ideas?
I am using SQL Server 2000. I need to query my database for all thecontracts that came in during a certain time frame (user is promptedfor reportingperiodid).Table - PeriodsFields - Reporting Period id intReporting Period desc varchar(30)Reporting Period Begin Date datetimeReporting Period End Date datetimeIf the user selects a 3 then the begin date is Jan. 1, 2004 and theend date is June 30, 2004.Now I need to calculate did any money come in for each week in thattime frame. I need to create a weekly list of all the weeks in thattime frame. Each time frame begins on a Monday. So my list wouldlook like1/5/20041/12/20041/19/20041/26/2004All the way to the end of that time period.How do I create this weekly list from a given time period using T-SQL?I would appreciate any and all help on this.Thanks,Tony
View 3 Replies View Related
I have a stored procedure that grabs the Region key from the user, and the date. But i want to set up 5 reports, for day of the week. So i want to make the second parameter a defualt parameter set by me in each report. which is better then having to create 5 stored procedures with the same info just different dates.
when i try to enter the same data i have in my stored procedure to get Mondays date, i get an error.
Here is what i entered in the report parameter default expression box for the date :
=DATEADD(dd, - 6, DATEADD(dd, 1 - DATEPART(w, GETDATE()), CONVERT(varchar, GETDATE(),
101))))
but i keep getting an error that says:
The Value expression for the report parameter €˜Daily_activity_statistics_datetime€™ contains an error: [BC30451] Name 'dd' is not declared.
Build complete -- 1 errors, 0 warnings
whats wrong with it? This works fine in the stored procedure for the where clause. Please help! this would save me alot of time.
In my below T-sql the shipment date is set to next Thursday, if the shipment is missed in current week. Now, I've to change the t-sql code to change the shipment dates to Monday instead of Thursday.
USE tempdb;
GO
DECLARE @Date DATE;
SET @Date = GETDATE();
--SET @Date = '2014-07-25';
DECLARE @TEST_DATA TABLE
[code]...
I would like to show employee work hours daily from Monday to Sunday. I have managed to write a query to get the total hours.
This is the query giving total hours from and to date.
(select sum(t.timespent)/60 from timeitems t
where t.employee = e.code
and t.project = p.code
and t.ndate >= '2014-07-15'
AND t.ndate <= '2014-07-15') as Hours
from projemplink pl
How to retrieve only Monday dates from each month in sql server.
If i pass any value (Let say GETDATE()) then i should get all monday values from the current month.
I want to calculate bi weekly range data in the sql.
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 hard coded 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
Here below is the perfect query i made which is working fine and giving me the sql output but just only need is how to convert to excel and automate the job scheduling so that it run on everyday and send the mail with attachment .
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn
FROM
(
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn, 1 [ordering] FROM alpha.dbo.DCADFeed
where sAMAccountName collate SQL_Latin1_General_CP1_CI_AS in
[Code] ....
I have written a piece of SQL which I thought was wokring fine. I wanted to show all data for the current week from Monday - Sunday
I wanted to pick up all data from Monday 00:00:01 to Sunday 23:59:59 and then when the data runs again on the next Monday, a completley new set of data starts to be loaded in
On the off chance I have come into work today and realise that no data is being diaplyed, and today is a Sunday
I know for a fact that my SQL will pick up data as soon as it hits 00:00:01 tonight
I really want my SQL to pick up data for a Sunday
BETWEEN DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) AND DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 6)