.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Question Regarding Date Range

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :Sql Server
I have a requirement to confirm how many employees were 'active' on a given date.  The employee table which I'm using contains the following fields (among others):  1.   Row_Start_Date (when an employee started with the company) 2.  Row_End_Date (when an employee terminated with the company (if applicable)) What would be the best way to design this?  For example, let's say an employee started on 1/1/2010 (Row_Start_Date) and is still employeed (Row_End_Date is null).  If an analyst wants to see how many employees were active on 5/1/2010, how do I design it so that this employee is counted.  If I build a date dimension off of Row_Start_Date, then this employee would be reflected on 1/1/2010, but not on 5/1/2010.  Hopefully this makes sense, and I appreciate any assistance/suggestions in advance... KoleKRS

View Complete Post

More Related Resource Links

Date range question

I have two date range tables

create table date1
effDate datetime,
expDate datetime

create table date2
effDate datetime,
expDate datetime

insert into date1 values('01/12/2006','06/19/2006')
insert into date2 values('01/01/2006','06/14/2006')

I want to cut 3 versions of these date ranges :-

01/01/2006 01/11/2006
01/12/2006 06/14/2006
06/15/2006 06/19/2006

How to achieve this through T-SQL ?

Achintya Jha, MCAD

ListViewControl - how to group items by their value, for eg. date range



I have a table containing courses, columns are 

Name - "Yoga Classes"

DateStart - "2/23/2010"

DateEnd - "2/24/2010"

I am using ListView Control to bind it, is there a way to display them and group them by Month? 

for eg.


Piano Class (10th Jan 2010 - 11th Jan 2010)
Guitar Class (10th Jan 2010 - 11th Jan 2010)


Yoga Class (15th Feb 2010 - 16th Feb 2010)

Thanks for help.

Need help in Date Range

HI Guys I need help to write a query I have a table Employee: empIDEmployeeName Task StartDt                                EndDt     1      xxx                  1    1950-01-01 00:00:00.000 1979-12-31 00:00:00.000     2     yyy                  2    1980-01-01 00:00:00.000 2009-06-30 00:00:00.000     3      aaa                 3    2009-06-30 00:00:00.000 2010-01-31 00:00:00.000     4     bbb                  4    2010-02-01 00:00:00.000 null when I pass one variable with date like 20070101 I need check the date ranges and get the exact record(this case 2nd records)

Ajax Calendar Extender Question (about disable pass date)

HiI am wondering if it has anyway to disable the past day on the Ajax Calendar Control, previously, when we use CalendarExtender, we need to put a asp:Calendar and a textbox. we can use FindControl to find the calendar control and disable the past days.I am wondering is there anyway  could do the same stuff?Another question is how could I read the user selected date to the Date variable?Do i need to convert the date from the TargetControl ? E.g. Convert.ToDateTime(txt_DATE.text) ?Please let me know if you want me to explain more.Many thanksChi 

Export Data to Excel Based on Date Range

I have data from multiple tables that I would like to display in a gridview via a stored procedure.  I need to select the data based on a date range and display it.  Once displayed I need to include a button that would allow the user to export the gridview data to an excel spreadsheet.  Does anyone have an out of the box solution or know of a tutorial I can use to accomplish this feat?  I am currently using 2.0 for this project. I am newish to .net. Thanks in advance. Here is my stored procedure that calls the data from multiple tables.USE [DATABASENAME] GO /****** Object: StoredProcedure [dbo].[GetUsersAll] Script Date: 09/03/2010 08:20:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetUsersAll] AS SELECT aspnet_Users.UserName, aspnet_Membership.Email, Team.Name, MemberTeamRole.InsertDate, MemberTeamRole.ManagerApprovedDate, MemberTeamRole.RegistrarApprovedDate, MemberTeamRole.RoleName, MemberInfo.firstname, MemberInfo.lastname FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId INNER JOIN MemberInfo ON aspnet_Users.UserId = MemberInfo.memberid LEFT OUTER JOIN MemberTeamRole ON aspnet_Membership.UserId = MemberTeamRole

Date Question SRSS

I need to calculate between two different fields. Example: John was booked again on 1-1-10 and release on 2-1-10 John was booked on into the jail on 4-1-09 and released on 4-12-09 I need to take the last release date and the next booking date and calculate how long they been away. So, I was thinking on using a datediff. =DateDiff( "m", Fields!ActualReleaseTime.Value, Fields!BookingDateTime.Value) =DateDiff( "m", "4-12-09", "1-1-10") How would I grab the next booking date record. Sniffles

MDX Date Range calculated member

Hi, I have [Sales Amount] and [Date]. How can I create a calculated member that will aggregate only the values from [Sales Amount] where month([Date]) is 'Jan' or 'Feb' and year is CurrentYear ? Then 'Mar' & 'Apr' and so on.... Thank you. D.

SSRS 2008 question? Date Help

Hi Experts, I'm new to SSRS world. I'm trying to print current date in the format of "Month-Name Date, Year", here is the t-sql query:- SELECT DATENAME(MONTH, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) + ', ' + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR(4)) AS [Month DD] --'September 14, 2010' Everything is working perfectly untill here, now I placed text-box with exp as :- =DATENAME(MONTH, NOW()) + " " + CAST(DAY(NOW()) AS VARCHAR(2)) + ", " + CAST(DATEPART(YEAR, NOW()) AS VARCHAR(4)) AS [Month DD YYYY] But it is giving error as :- The Value expression for the text run "Textbox22.Paragraph[0].TextRuns[0]' contains an error: Name 'DATENAME' is not declared. I understood the error, saying DATENAME is not the appropriate function in SSRS, so my question is can somebody write me correct code to give me o/p in the form of my above t-sql query   Any Help? I'm using SSRS 2008 Thanks Regards, Kumar

Date Range Problem

How would I go about returning the closest ReleaseDate to DateIn? The record I would like to return is (2010-07-18 00:00:00.000 2010-07-17 00:00:00.000) CREATE TABLE #test ( ID INT ,DateIn DATETIME ,ReleaseDate DATETIME ) INSERT INTO #test VALUES(1, '07/18/2010', '01/10/2010') INSERT INTO #test VALUES(2, '07/18/2010', '07/17/2010') SELECT * FROM #test WHERE CONVERT(CHAR(10), '07/17/2010', 101) <= CONVERT(CHAR(10), '07/18/2010', 101) AND CONVERT(CHAR(10), '07/18/2010', 101) <= CONVERT(CHAR(10), '01/10/2010', 101) DROP TABLE #test  

about date in database question

hi i need to find data between 2 date's and time's. i use one field for date ,  and one field for time. **is it be better to use only one field for date & time ?** i see that it came in  `dd/mm/yyyy hh:mm:ss`  format that  can contain date and time. this question is for acceess and for sql-server thank's in advance

region - date & time question


in my C# program i receive date & time like this: `DateTime.Now` 

and i get: `19/09/2010 20:10:30` because my region is: `Hebrew (Israel)`

but What happens if I'll install my program on computer with region `English (united states)`

I'll probably get an error because the format is `MM/dd/yyyy hh:mm:ss`

my question is, How to ensure that i Always get the date in `dd/MM/yyyy hh:mm:ss` format ?

in any type of region ?

thank's in advance

T-SQL query, average of daily time periods over a date range



I'm building a report in Crystal Reports using a SQL command against a T-SQL 2005 telephony database.

I need to be able to run the query across a given datetime range, 6 months for example and bring back a 5 day display (Mon-Fri) with a group for every 15 minute interval in each day.

The group figures need to contain an average of the amount of calls presented for each 15 minute interval on any day across the whole datetime range, so for example the Monday 10:00 - 10:15 figure would be an average of all calls presented in every 10:00-10:15 range on each of the Mondays that fall within the datetime range.

I've got a query built now that gives total presented figures grouped by these intervals across one week but I can't figure out how to do this average function across a range.

Does anyone have any idea how I'd go about accomplishing this? I'm pretty new to SQL but keen to learn so any pointers on functions to research etc would be very much appreciated.

Thanks alot in advance, Andy.

Query for detail view across one week included below for table/fields etc...



count(DISTINCT Calls.SessionID) as Presented, min(Calls.startDateTime) as DateTime

INNER JOIN QueueDetail
ON Calls.sessionID =  Queues.sessionID
AND Calls.sessionSeqNum =  QueueDet

Validate (Javascript) if input date is within this range...


I'd like to validate if the inputed date is within this range:

3 weeks before today's date to 3 weeks after today's date 

The following Javascript code checks if the date is within either today's date or 2 weeks before:

var dateSystem = (dateToday.getMonth() + 1) + dtCh + dateToday.getDate() + dtCh + dateToday.getFullYear();
            var prev = new Date();
            //past date 14 dates before today
            prev.setDate(prev.getDate() - 14);
            var pastDate = (prev.getMonth() + 1) + dtCh + (prev.getDate()) + dtCh + prev.getFullYear();
            var time1 = new Date(dateSystem).getTime();
            dtStr = month + dtCh + day + dtCh + year;
            var dateuser = new Date(dtStr);
            var time2 = dateuser.getTime();
            if (time1 - time2 <= 1209600000 && time2 <= time1) {
                return true;
            } else {
                alert("Vaccines/Services date must be within this date range: " + pastDate + " to " + dateSystem);
                return false
            return true


I'm not sure how to modify. I actually got the code above from these forums.

Help is much appreciated!

Performance problem with date range JOINs



when I am joining two tables where one table has two columns which specify a date range and the other table has one column with a single date which must be in that range for a join, then the performance is not so hot. The T-SQL example only shows the basic query scheme, in reality there are appropriate indexes (but not on the date columns since I found them not helpful) and the DateRange column has about 100 mio rows and the Incident table about 200,000 rows. The query currently takes hours, I must speed it up by at least factor 10.

TIA for any ideas. Regards

	[DateRangeID] [int] NOT NULL PRIMARY KEY,
	[RangeTypeID] [smallint] NOT NULL,
	[StartDate] [date] NOT NULL,
	[EndDate] [date] NOT NULL)

	[IncidentID] [int] NOT NU

Query acting weird.. larger date range works in 1min 26sec and a smaller range say 1 month - 3 month


I have no clue why my query is acting weird

If i try to run it for 1/1/2010 9/30/2010 the query takes around 1min 26 sec and  return around a million rows

and If i run for 8/1/2010 to 8/31/2010 it takes forever to it...

basically i am getting data from 5 tables and putting it in a temp table and then updating that temp table 2-3 times with some information and then displaying it.

I am stumped as to why it works fast for a longer date range and runs the  snail for a small period of time..

I am in the verge of pulling my hair and going crazy..

any help will be appreciated.




Date range checking


Suppose I have a date range:

             15/09/2010  -  25/09/2010 

my user will provide another date range for example:       17/09/2010  -  22/09/2010

I want to check that user provided date range is within my date range.

Checking works if both of user provided dates are between my range. But I need to check if one of user provided date is in my date range and another is out of my date range. For example:

07/09/2010  -  22/09/2010

17/09/2010  -  27/09/2010

Here 22/09/2010 is in my date range and 17/09/2010 is also in my range.

I need to write a sql query that check user dates are in my dates.

Any help will be appritiated.

Date range queries


Hi All,


We have several reports where the user can specify a date range for the where clause, i.e. {[Date].[Date].[01 Jan 2008]:[Date].[Date].[ 01 Jan 2010]}. Since the date range can be very large, we are just wondering if the query processor can generate “smarter” execution plans so that the Aggregations on year and month level can be used…


Thanks in advance for your help and input.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend