Calc DateDiff In Workdays Only, No Holidays Either
Mar 26, 2008
I use the NETWORKDAYS(start_date, end_date,holidays) function in Excel and Access regularly. I'm trying to find a similar function in TSQL to use on Server2005. NetWorkDays allows me to calculate the difference between my ticket open and close dates while excluding weekends and holidays, (we are nice to our staff and let them off).
I'm trying to find an easy way to do this in TSQL without writing a large amount of code.
I will greatly appreciate any help.
View 1 Replies
ADVERTISEMENT
Jul 18, 2006
Hi everyone,
I'm hoping someone can help me with some sql statements. I have a temp table that contains 30 dates that a student has missed in the last year. I also have a holiday table of when training was not available. I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table). I know this is some nasty looping statement but I can't get my brain around it.
I would like do this in a stored proc but I could use C# if necessary.
Thanks in advance, Jessica
View 7 Replies
View Related
Jan 24, 2006
Hi people. I hope you can help with this. It has me completely stumped.
I am trying to get SQLS 2000 to calculate days between dates, without any success at all.
The two dates are as imported from a text file, and are as follows: -
(@issueddate=) 3/01/2006 3:37:00 PM
(@completiondate=) 04-FEB-2006 18:00
I cannot do much about the date formats, as they are as received from another party, as an email attachment.
I have tried just about everything I know to get this working, even tried commenting out each line till practically nothing is left, and still get the same message. Have also deleted the procedure and recreating it.
I have tried removing "PM" from the first date; tried changing varchar to datetime; tried using 'dd' instead of 'd' in the DateDiff function
Because of the "Aborted" response in Query Analyser, nothing is returned, and yet the Query Analyser's "Execute"
Command reports it completed successfully.
Should I ignore this "Aborted" response altogether and assume that it works?
Is it a known bug in SQLS 2000?
Here is the Stored Procedure:
----------------------------
CREATE procedure proc_calcDeadline
(@issueddate varchar(30)='',@completiondate varchar(30)='',@return int=NULL output)
as
declare @Period int
--set @issueddate='3/01/2006 3:37:00 AM'
--set @completiondate='04-FEB-2006 18:00'
set @period=datediff(d,@issueddate,@completiondate)
--print 'period=='+cast(@period as varchar)
set @return=@period
return @return
GO
The DEBUG response in Query Analyser is as follows: -
"Aborted"
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
When I make changes in Query Analyser, (ie, "Alter Procedure', etc" then select "EXECUTE" option
the response is: -
"The command(s) completed successfully."
PS I am STILL unable to get fully into Query Analyser for step-by-step processing, even though I created the database on a single workstation (and therefore am the dbowner, with full Admin rights).
(Homesick for VBA territory, where I can get date calcs like this done easily!!)
Lester Vincent
Sydney
View 5 Replies
View Related
Nov 27, 2007
Hi,
I have a table with 2 columns, a datetime column (date) and a bit column (workday)
This is a production calender. It's filled with all dates till 2020/12/31
I need a query to find the date of the n-th workday given a specific start date.
Using Sql Server 2000.
For example
Date Workday
--------------------
2007/11/01 0
2007/11/02 0
2007/11/03 1
2007/11/04 1
2007/11/05 0
2007/11/06 1
2007/11/07 1
Given the date 2007/11/03, the 2nd workday would be 2007/11/06
Thank's
Perry
View 8 Replies
View Related
Oct 24, 2007
This is one way you can add a number of workdays to a specific date and return a workday (monday-friday).CREATE FUNCTION [dbo].[fnAddWorkdays]
(
@StartDate DATETIME,
@DaysToAdd INT
)
RETURNS DATETIME
AS
BEGIN
RETURN@StartDate
+ CASE(@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate) - 2)) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END
+ @DaysToAdd / 5 * 7
+ @DaysToAdd % 5
+ CASE WHEN (@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate + CASE (@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate) - 2)) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END) - 2)) % 7 + @DaysToAdd % 5 > 4
THEN 2
ELSE 0
END
END
E 12°55'05.25"
N 56°04'39.16"
View 6 Replies
View Related
Dec 13, 2007
//Sorry my English!
I have got a two datetime field in my table. Example
startDate EndDate
----------------- ------------------
2007-02-05 23:36:00.000 2007-02-14 05:47:00.000
How to i calculate workhours between my two dates without saturday and sunday days and out of the work time (08.00 and 20:00 workhours)
Please help
View 1 Replies
View Related
Feb 10, 2006
New SQL - but certainly enjoying the experience.
I need to calculation an Average in SQL -
A view I created contains:
Item WorkOrders TotalProduced
A 10 125
B 1 10
C 3 48
Desired Results
Item AVGPRODUCED
A 12.5
B 10
C 16
The view will be used by a Crystal Report so I could calculate in Crystal - But I was wondering how to in SQL
Any help would be appreciated
View 6 Replies
View Related
Jun 29, 2007
I have a report that requires 2 "tables". The first table summarizes total
lbs by a category and then provides a company total at the end:
cat 1 75
cat 2 100
cat 3 200
-------
total 375
The second table needs to display the % of the total for each category:
cat 1 20%
cat 2 27%
cat 3 53%
-------
total 100%
How can I reference the company total for doing the calculations for the
second table? I am working with Visual Studio 2003. My dataset pulls a
file from the AS400 using an sql statement.
Thank you, PB
View 4 Replies
View Related
Apr 6, 2005
hi,
I'm trying to calculate how much unused space i have on one datafile. My main goal is to determine the max space i can save by doing a dbcc shrink.
Any help is greatly appreciated.
ThanKs in advance.
Al
View 1 Replies
View Related
Feb 2, 2008
Hello
In my ASP 2.0 website, I wish to arrange holidays and specific days from database with using Sql Server. What SQL query I must add to this following code? If you can post the required codes, I'd be most thankful. Regards.private DateTime _siparis;public DateTime Siparis
{
get { return _siparis; }set { _siparis = value; }
}protected void Button1_Click(object sender, EventArgs e)
{Timer1.Enabled = true;
DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);Session["siparis"] = sonTarih;
TimeSpan aradakiFark = sonTarih - DateTime.Now;StringBuilder sb = new StringBuilder();
sb.Append("Remaining time is ");if (aradakiFark.Days > 0)
{
sb.Append(aradakiFark.Days.ToString());sb.Append(" days ");
}if (aradakiFark.Hours > 0)
{
sb.Append(aradakiFark.Hours.ToString());sb.Append(" hours ");
}if (aradakiFark.Minutes > 0)
{
sb.Append(aradakiFark.Minutes.ToString());sb.Append(" minutes ");
}
lblSure.Text = sb.ToString();
}protected void Timer1_Tick(object sender, EventArgs e)
{DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);
TimeSpan aradakiFark = (DateTime)Session["siparis"] - DateTime.Now;StringBuilder sb = new StringBuilder();
sb.Append("Remaining time is ");if (aradakiFark.Days > 0)
{
sb.Append(aradakiFark.Days.ToString());sb.Append(" days ");
}if (aradakiFark.Hours > 0)
{
sb.Append(aradakiFark.Hours.ToString());sb.Append(" hours");
}if (aradakiFark.Minutes > 0)
{
sb.Append(aradakiFark.Minutes.ToString());sb.Append(" minutes ");
}
lblSure.Text = sb.ToString();
}
}
I've tried a select query that uses getdate and returns me to the manually arranged holidays in the Holidays table, but I've failed. I wish to do that, this query arranges holidays and adds +1 day on that holiday, if this one day added day is a holiday again, than adds one day again. How can I write this code? Thanks.
View 1 Replies
View Related
Jul 20, 2005
Hi!I have a large table in SQL Server 2000 with a datetime-column 'dt'. I wantto select all rows from that table, excluding days which fall on holidays orweekends. What is the best way to accomplish this? I considered creating anew table called "holidays" and then selecting all rows (sort of "where notin (select * from holidays)") , but I was looking for a better solutionsince that implies that I have to populate the "holidays" table.Suggestions are welcome!Sincerely,Nils Magnus Englund
View 2 Replies
View Related
Feb 1, 2008
Hello
In my ASP 2.0 website, I wish to arrange holidays and specific days from database with using Sql Server. What SQL query I must add to this following code? If you can post the required codes, I'd be most thankful. Regards.
private DateTime _siparis;
public DateTime Siparis
{
get { return _siparis; }
set { _siparis = value; }
}
protected void Button1_Click(object sender, EventArgs e)
{
Timer1.Enabled = true;
DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);
Session["siparis"] = sonTarih;
TimeSpan aradakiFark = sonTarih - DateTime.Now;
StringBuilder sb = new StringBuilder();
sb.Append("Remaining time is ");
if (aradakiFark.Days > 0)
{
sb.Append(aradakiFark.Days.ToString());
sb.Append(" days ");
}
if (aradakiFark.Hours > 0)
{
sb.Append(aradakiFark.Hours.ToString());
sb.Append(" hours ");
}
if (aradakiFark.Minutes > 0)
{
sb.Append(aradakiFark.Minutes.ToString());
sb.Append(" minutes ");
}
lblSure.Text = sb.ToString();
}
protected void Timer1_Tick(object sender, EventArgs e)
{
DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);
TimeSpan aradakiFark = (DateTime)Session["siparis"] - DateTime.Now;
StringBuilder sb = new StringBuilder();
sb.Append("Remaining time is ");
if (aradakiFark.Days > 0)
{
sb.Append(aradakiFark.Days.ToString());
sb.Append(" days ");
}
if (aradakiFark.Hours > 0)
{
sb.Append(aradakiFark.Hours.ToString());
sb.Append(" hours");
}
if (aradakiFark.Minutes > 0)
{
sb.Append(aradakiFark.Minutes.ToString());
sb.Append(" minutes ");
}
lblSure.Text = sb.ToString();
}
}
View 3 Replies
View Related
Aug 9, 2004
Hi,
I'm working on a helpdesk project and I require the calculation of the holidays.
I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel.
Thanks.
Madhavi.
View 2 Replies
View Related
May 31, 2006
Hello,
First things first - I have a table which holds employee information (tbl_EmployeeDetails), and another table which holds information about the holidays they have booked (tbl_Holidays).
If an employee books 5 days off, 5 rows will appear in the tbl_Holidays table, each line showing 1 under the day field and 7 under the hours field.
I'm trying to produce a function which will do the following :
1) Pass in the employee number
2) Establish whether the employee works full time or part time by looking up to tbl_employeedetails, and checking the fulltime flag
3) If full time, look up to tbl_Holidays and count number of days
4) If part time, look up to tbl_Holidays and count number of hours
5) After this, return the number of holidays booked
My code is as follows :
=============
CREATE FUNCTION [dbo].[fn_Get_Booked_Holidays_Current_Year] ( @EmpNo int )
RETURNS Float
AS
BEGIN
-- Declare fields
DECLARE @FullTime int
-- Determine if Part Time or Full Time
SET @FullTime = SELECT FullTime FROM tbl_EmployeeDetails
IF @FullTime = 1
SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef
FROM dbo.tbl_Holidays
GROUP BY EmployeeNumber
HAVING (EmployeeNumber = @EmpNo)
IF @FullTime = 0
SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef
FROM dbo.tbl_Holidays
GROUP BY EmployeeNumber
HAVING (EmployeeNumber = @EmpNo)
END
==========
Can someone please let me know where I'm going wrong, or what I need to do to get this function done?
Thanks,
J
View 2 Replies
View Related
May 19, 2014
I created a dbo.Calendar table that stores dates and a work day flag (1=work day, 0=non-work day) so I can use it to calculate the next business date from a date using a function. I'm using a while group to count only the work days and a couple other internal variables but I'm not sure if I can even use them in a function.
Assuming Sats & Suns are all non-work days in April 2014, if my @WorkDays = 10 for 10 work days and my @DateFromValue - 4/1/2014, I would expect my return date to be 4/15/2014.
------ Messages after I click execute on my query window that has my function ------------------------------------------------------
Msg 444, Level 16, State 2, Procedure FGetWorkDate, Line 19
Select statements included within a function cannot return data to a client.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 20
Invalid column name 'WorkDay'.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 22
Invalid column name 'Date'.
------ my function code ----------------------------
CREATE FUNCTION [dbo].[FGetWorkDate](
@WorkDays VARCHAR(5),
@DateFromValue AS DateTime )
RETURNS DATETIME
[Code] ....
View 10 Replies
View Related
May 18, 2015
I inherited a report that counts patient visits per month. Holidays are hard coded into the query. What is the best way to handle holidays without hardcoding?
View 4 Replies
View Related
Jul 23, 2005
Is there an equivalent function in SQL to the'NETWORKDAYS(start_date,end_date,holidays)' function in Excel ?NetworkDays in Excel returns the number of whole working days betweenstart_date and end_date. Working days exclude weekends and any datesidentified in holidays. This part isn't vital but would be nice tohave. The weekend stuff is more important.I realise this will most likely need to be some method I write myself(possibly based on the name of the day - Mon-Fri), but any pointerswould be appreciated if anyone has done this before.ThanksRyan
View 2 Replies
View Related
Jun 4, 2008
The below code works fine to measure the difference in days between two dates.
However, there is an additional business requirement to subtract week-ends, and holidays, from the equation.
Any ideas on how to accomplish this task, and leverage the below, existing code? Thanks in advance!
(SELECT ABS((TO_DATE(TO_CHAR(" & ToFieldDate & "),'yyyymmdd') - TO_DATE(TO_CHAR(" & FromFieldDate & "),'yyyymmdd'))) FROM DUAL) AS Measurement "
View 2 Replies
View Related
Aug 28, 2013
I have a Bank holiday table (ID column, and Date column), how to exclude bank holidays in the datediff returned.
Create FUNCTION [dbo].days_diff ( @date1 datetime,@date2 datetime )
RETURNS int
AS
BEGIN
declare @i int
Declare @count int
[Code] .....
View 4 Replies
View Related
May 12, 2005
Also includes ISOWeek & Weekends
Dont know whether this is of any use to anyone or it has been done before but there are a lot of posts on here regarding date calculation issues & usually the most straight forward answer is to compare against a table of dates.
So while looking at Bretts blog and another post on here, i thought i'd post this on here
http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49698
Special thanks to rockmoose & BOL (as always)
Edit: It also moves bank holidays to the following Monday (and Tuesday - xmas) if the bank holiday(s) falls on the weekend
SET DATEFIRST 1
SET NOCOUNT ON
GO
--Create ISO week Function (thanks BOL)
CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END
GO
--END ISOweek
--CREATE Easter algorithm function
--Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689)
CREATE FUNCTION fnDLA_GetEasterdate(@year INT)
RETURNS CHAR (8)
AS
BEGIN
-- Easter date algorithm of Delambre
DECLARE @A INT,@B INT,@C INT,@D INT,@E INT,@F INT,@G INT,
@H INT,@I INT,@K INT,@L INT,@M INT,@O INT,@R INT
SET @A = @YEAR%19
SET @B = @YEAR / 100
SET @C = @YEAR%100
SET @D = @B / 4
SET @E = @B%4
SET @F = (@B + 8) / 25
SET @G = (@B - @F + 1) / 3
SET @H = ( 19 * @A + @B - @D - @G + 15)%30
SET @I = @C / 4
SET @K = @C%4
SET @L = (32 + 2 * @E + 2 * @I - @H - @K)%7
SET @M = (@A + 11 * @H + 22 * @L) / 451
SET @O = 22 + @H + @L - 7 * @M
IF @O > 31
BEGIN
SET @R = @O - 31 + 400 + @YEAR * 10000
END
ELSE
BEGIN
SET @R = @O + 300 + @YEAR * 10000
END
RETURN @R
END
GO
--END fnDLA_GetEasterdate
--Create the table
CREATE TABLE MyDateTable
(
FullDate datetime NOT NULL CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED,
Period int,
ISOWeek int,
WorkingDay varchar(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y'
)
GO
--End table create
--Populate table with required dates
DECLARE @DateFrom datetime, @DateTo datetime, @Period int
SET @DateFrom = CONVERT(datetime,'20000101') --yyyymmdd (1st Jan 2000) amend as required
SET @DateTo = CONVERT(datetime,'20991231') --yyyymmdd (31st Dec 2099) amend as required
WHILE @DateFrom <= @DateTo
BEGIN
SET @Period = CONVERT(int,LEFT(CONVERT(varchar(10),@DateFrom,112),6))
INSERT MyDateTable(FullDate, Period, ISOWeek)
SELECT @DateFrom, @Period, dbo.ISOweek(@DateFrom)
SET @DateFrom = DATEADD(dd,+1,@DateFrom)
END
GO
--End population
/* Start of WorkingDays UPDATE */
UPDATE MyDateTable
SET WorkingDay = 'B' --B = Bank Holiday
--------------------------------EASTER---------------------------------------------
WHERE FullDate = DATEADD(dd,-2,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Good Friday
OR FullDate = DATEADD(dd,+1,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Easter Monday
GO
UPDATE MyDateTable
SET WorkingDay = 'B'
--------------------------------NEW YEAR-------------------------------------------
WHERE FullDate IN (SELECT MIN(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dw,FullDate) NOT IN (6,7)
GROUP BY DATEPART(yy,FullDate))
---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------
OR FullDate IN (SELECT MIN(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------
OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 8 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
--------------------XMAS(Move to next working day if on Sat/Sun)--------------------
OR FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN
DATEADD(dd,+2,FullDate) ELSE FullDate END
FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25,26))
GO
---------------------------------------WEEKENDS--------------------------------------
UPDATE MyDateTable
SET WorkingDay = 'N'
WHERE DATEPART(dw,FullDate) IN (6,7)
GO
/* End of WorkingDays UPDATE */
--SELECT * FROM MyDateTable ORDER BY 1
DROP FUNCTION fnDLA_GetEasterdate
DROP FUNCTION ISOweek
--DROP TABLE MyDateTable
SET NOCOUNT OFF
Andy
Beauty is in the eyes of the beerholder
View 4 Replies
View Related
Feb 2, 2014
I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.
CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime)
RETURNS int
AS
BEGIN
/*
Description:
Function designed to calculate the number of business days (In hours) between two dates.
[Code] ......
View 4 Replies
View Related
Nov 15, 2007
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)
View 1 Replies
View Related
Dec 5, 2005
If I wanted to get everyone DOB who is over 18 how would I do that? I am currently trying something like this, but no luck...
Declare @todays_date datetime
Select from person
CASE
WHEN dateadd(year, datediff (year, Date_Of_Birth, @Todays_Date), Date_Of_Birth) > @Todays_Date -- Date of Birth check
THEN datediff (year, Date_Of_Birth, @Todays_Date) - 1
ELSE datediff (year, Date_Of_Birth, @Todays_Date)
END
>= 18
View 3 Replies
View Related
Jul 27, 2004
I'm using a datediff(mi, start, stop) to get the duration of an operation. i want it displayed in HH:MM format. can anyone help me w/ a way to do that????
tia,
e3witt
View 4 Replies
View Related
Mar 9, 2004
Hello,
My string is:
SELECT weight FROM progress WHERE dateInput = (SELECT MAX(dateInput) FROM progress) AND memberID = 1
The problem is that the MAX dateInput doesn't belongs to memberID 1. It belongs to memberID 2. What I want is that I wanna choose the MAX Date of memberID 1. I thought of maybe using datadiff function. But I don't know how to make the datediff statement. Maybe I can write the datediff statement whereby the least datediff between the dateInput and getdate() will be the row I want. I really appreciate the person that helps me this problem... Thanks 1st of all!
View 2 Replies
View Related
Apr 15, 2004
I am trying to select records from whatever the current date would be and 12 months before whatever the current date is. How would I go about doing this. The table that I am trying to do this with has a year column and a month column.
I was playing with the date diff function, but I can only get dates from the specified date range. I need it to be where if I run it tomorrow, it will get that day and everything within the last 12 months.
View 7 Replies
View Related
Apr 25, 2008
I need to get a listing of all persons who are atleast 18 years of age. A date of birth field in the database is in this format:
4/25/2008 12:00:00 AM
My solution would be where the difference between the current date and the dob is >= 18.
I tried...
select * from table where datediff(yy,dob,getdate)) >= 18.
But this only seems to subtract the years and ignore the days/months, which I need. Could anyone provide the syntax I need?
Help is appreciated. Thanks.
View 3 Replies
View Related
Jun 24, 2008
Hi there i am using the datediff funtion but it does not seem to be inclusive of the two dates eg
SELECT DATEDIFF(day, s_Date, e_date) AS NumberOfDays,*
FROM weekendtest
i know i could do
SELECT DATEDIFF(day, s_Date, e_date)+1 AS NumberOfDays,*
FROM weekendtest
but i was just wondering if there some other function i should use that would be more appropriate
View 3 Replies
View Related
Oct 21, 2004
Hi
I've a problem!!!
i want to use datediff thats no problem. But the first parameter has to
be parameterised. What datatype do i declare it as. i declare it as varchar
it returns me an error : Invalid parameter 1 specified for datediff.
i'm doing this in a stored procedure.
Is there a way out or do i've to use the good old 'IF' or 'Case' ?
regards
pradeep
View 1 Replies
View Related
Oct 19, 2006
Hi,
I have a Table Where I have Two Date columns and I want to find the Differnce in the 3rd column.ie DateReceived - AsOnDate = NoofDays.I tried this code
SELECT SRNO,CONVERT (VARCHAR,AsonDate,102)as ASONDATE,CONVERT(VARCHAR, DateReceived, 102) AS DateReceived,CONVERT(VARCHAR, DateAcknowledged, 102) AS NoofDays,
sum(CASE WHEN DATEDIFF(DAY, DATERECEIVED,ASONDATE, GETDATE())
FROM Details
Plz help me...
Thanks
View 7 Replies
View Related
Jan 27, 2008
SELECT DATEDIFF(mm,0,'01/25/2008')
Returns 1296 what does it represent...
Thanks
View 2 Replies
View Related
Feb 29, 2008
Could somebody please explain to me why I get the following results:
select datepart (ww, '10/02/08') --returns 6
select datepart (ww, '18/02/08') --returns 8
select datediff (ww, '10/02/08', '18/02/08') -- returns 1
I was expecting the third query to return 2.
Thanks.
View 11 Replies
View Related
Nov 23, 2006
Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00.
My code :
datediff(Minute,'01:00:00','23:00:00')
The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours)....
Can anybody help ???
Thanks in advance...
View 2 Replies
View Related
