|
I need to calculate the number of weekdays between two dates in SQL. Does someone know an easy way for that?
|
|
|
|
|
Hi
Do you mean number of weeks between between two dates ?? Then use DATEDIFF() function
Eg: SELECT DATEDIFF (ww, '01/01/2007',getdate() will 10 weeks
Harini
|
|
|
|
|
No, I meant the number of weekdays. For '01/01/2007' and getdate() it should return 51 (as of today). So to be more precise I need something like this in SQL:
DateTime start = new DateTime(2007, 1, 1);
int num = 0;
while (DateTime.Compare(DateTime.Today, start) == 1)
{
int day = (int)start.DayOfWeek;
if (day > 0 && day < 6)
num++;
start = start.AddDays(1);
}
-- modified at 10:47 Tuesday 13th March, 2007
|
|
|
|
|
I feel a bit silly now as I noticed I basically answered my own question. All I had to do is rewrite the sample provided above in SQL, using the same logic.
|
|
|
|
|
Hi
You can also try this:
SELECT DATEDIFF (dd, '01/01/2007',getdate()) - (DATEDIFF (wk, '01/01/2007',getdate()) * 2)
Harini
|
|
|
|
|
SELECT DATEDIFF (dd, '03/11/2007', '03/17/2007') - (DATEDIFF (wk, '03/11/2007','03/17/2007') * 2) returns 6 and that's wrong.
|
|
|
|
|
Hi
Check this link:
SQL Forums[^]
Copied and pasted example from the above link:
CREATE function dbo.DateDiffWeekdays
(@fromdate datetime,
@todate datetime)
RETURNS int
AS
begin
declare @procdate datetime, @enddate datetime
declare @weekdays int
set @procdate = @fromdate
set @weekdays = 0
while (@procdate < @todate)
begin
if (datepart(dw, @procdate + 1) <> 1) and (datepart(dw, @procdate + 1) <> 7)
set @weekdays = @weekdays + 1
set @procdate = dateadd(d, 1, @procdate)
end
---
if @todate is null
set @weekdays = null
return @weekdays
end
-- modified at 5:52 Wednesday 14th March, 2007
But still this is wrong, if you give between Mar 1st 2007 and March 3rd 2007
Harini
|
|
|
|
|
Actually that's very similiar to what I came up with rewriting my own code in SQL. Mine's:
CREATE FUNCTION WeekDaysBetween
(
@from datetime,
@to datetime
)
AS
BEGIN
DECLARE @num int
SET @num = 0
WHILE (DATEDIFF(day, @from, @to) > 0)
BEGIN
DECLARE @day int
SET @day = DATEPART(dw, @from)
IF (@day > 1 AND @day < 7)
SET @num = @num + 1
SET @from = DATEADD(day,1 , @from)
END
RETURN @num
END
This seems to work. Thanks for your help though.
|
|
|
|
|
Hi,
You can use SELECT with the same method you used to get the above results.
Please find this site http://www.avatto.com/ which is good for any database related queries...
|
|
|
|
|
I m using SQL Server 2005 & creating Reports using SQL Reporting.
I have one main report and one sub report.
I have called the sub report as hyperlink on one of the field from main report.
When i run the main report, i get the view properly.
But when i click on hyperlink from main report, that time i supposed to view the sub report
And i m using the input parameters to that sub reports.
But dont have any idea that how i can pass the parameters from main report dataset to sub report report dataset , in runtime !
please guide me for this..
Prakash Sawant
|
|
|
|
|
|
Hi
What is your question?? it is blank here.
Harini
|
|
|
|
|
Harini Krishnaswamy wrote: What is your question??
Read the subject line
|
|
|
|
|
Which database? I'm assuming you mean SQL Server
select name, filename from sysfiles
|
|
|
|
|
I assume you are asking for your database MDF file location , Under default installation settings, its in C:\Program Files\Microsoft SQL Server\MS SQL 1.0*\Data
* dont remember it exactly
Unless you change your installation folder, files are placed here by default (no matter if SQL 2000 and 2005 are both installed on your machine).
|
|
|
|
|
kaliem wrote: I assume you are asking for your database MDF file location
You appear to have replied to the wrong person. I never asked the question. I answered it.
Also, it is very easy to change the location of a database so relying on default folders is unwise. This is why my answer showed how to retrieve the location of the database files.
|
|
|
|
|
I assume any reply here would count a reply to this thread and anyone can view it. Sorry for any confusions.
Yeah, relying on default folders is not good. Because of nature of that query, I wanted be more specific. Chill out
|
|
|
|
|
kaliem wrote: I assume any reply here would count a reply to this thread and anyone can view it. Sorry for any confusions.
Yes, anyone can view it. However, this is a properly threaded forum so you have to reply to the appropriate person or it appears in the wrong place in the thread.
kaliem wrote: Because of nature of that query, I wanted be more specific.
|
|
|
|
|
what is is and where we can use...
|
|
|
|
|
Renuka Reddy wrote: what is is and where we can use...
I'm not entirely sure what you mean.
Do you mean sysobects?
The following will return all objects in the current database in SQL Server 2000
SELECT * FROM sysobjects
|
|
|
|
|
Give me Please Syntax's for locks with example if possible....
exclusive locks
updated locks and shared locks.. with lock modes
|
|
|
|
|
|
Good afternoon. I have a VB 6.0 app using ADO 2.7 to access SQL Server 2000. I have a nText field that holds Cyrillic text in which the data looks correct when viewing it in Query Analyzer. When retrieving the data via an ADO 2.7 recordset and looping through the records, the data appears as ???? in the recordset object itself. It appears the issue is the fact that ADO is not translating/recognizing the Cyrillic text, but I have read that ADO 2.7 is Unicode compatible. I have seen other posts with similar questions on various boards, but I have yet to come across an answer. If anyone can help with this situation, it would be greatly appreciated.
--Jason
|
|
|
|
|
Hi
I need to implement search facility. How can I do that ? How can I make a search index table ? How google is maintaining their search index ? Will they store all contents from the pages what they crawled ?
|
|
|
|
|
Hi Guys,
I need a hand with the following.
I need to write a stored proc that accepts a parameter that contain multiple values.
i.e
alter proc spGetLastNames
@Firstnames varchar(200)
as
Select lastname from user where firstname in(@Names)
@Firstnames should be able to accept a comma delimited string i.e 'Frank', 'Peter', 'William'
I am pondering whether to use XML to pass the Parameters, do you think this would be viable?
|
|
|
|