.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

Between date question help

Posted By:      Posted Date: October 28, 2010    Points: 0   Category :Sql Server

I have a database column of type datetime.

I want to query the table to get the number of records between 2 sets of dates.

The problem is the last date part of the between statement ignores that date in the datetime field.

Here is my query



So any dates that are on 2/12/2009 are skipped since their datetime is greater than 2/12/2009 such as 2/12/2009 10:11:04 AM etc.

How do I include that last date parameter in my results. I realize between should ignore the 2/12/2009 10:11:04 AM example. How do I fix it?

View Complete Post

More Related Resource Links

Question Regarding Date Range

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

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 

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

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

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

date format examples: Dumb question


I can't find a table that shows what different SSIS data flow date formats look like.

What IS a dt_dbdate, vs. a dt_dbtime or a dt_dbtimestamp?

I know these must be right under my nose on MSDN somewhere. 


All I can find is http://msdn.microsoft.com/en-us/library/ms141704.aspx.

If there's "Business Intelligence", there's bound to be "Business Stupidity".

Date Question


I have a case when today is tuesday I have to run a query for a date range consisting of Monday through Sunday the week before. so the where clause is something like this Where invoice_dt between (datepart(d,2,invoice_dt) and (datepart(d,1,invoice_dt)

But I need to make sure it only pulls data from last week.  I have tons of information (years) in this table and I don't want to pull more info than the most recent week.

SSIS - Passing a date parameter to a SQL Query question again....


I need to pass a date string to my sqlcommand inside of my "datareader source".  I read some of the questions about setting expression variables but I can't seem to figure out how to do it.  I pasted the query below.  The table name contains a date in it so I need to pass that date as a string into my sqlcommand at runtime.  Do I create a variable with the entire SQL command in it or can I just create a single variable that holds the date and just reference that in my datasource reader sqlcommand property?  Can someone assist me with the syntax for creating the expression?

     pages_20101006.`date` AS full_date,
     pages_20101006.`cat` AS category_no,

Thanks very much,



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

Newbie Question: How to sum a selected date and previous dates


I'm trying to get an employee count based upon the user selecting a given date in my date dimension.  My fact table contains a list of all employees that are 'Active', and my date dimension (Query Date) is joined to 'Hire_Date' in my fact table.  My dilemna is that if a user selects a given date, it only returns the employees who were hired on that date.  What I would like to do is create a calculated member that will show all employees hired on that date + those hired before that date.  Can anybody help me with this...I would appreciate it in advance...



Question on Reporting Services 2005 Date from iseries to ssrs

I was wondering I am using SQL server reporting services 2005 and I-series to gather my data.
I need to convert a date from yymmdd to mmddyyyy to use the calendar in a parameter. Is there anyway to do this? I have tried many different ways with no success. basically because the date in the i-series is 110414 (today's date) The datetime option on parameter will not work. I can use it as a string and type it in. I would really like to convert it to 04/14/2011 so that I can let the user select from a calendar. Let me know if you know anyway or anywhere I can post it to get an answer to this question.
Thank you

Using a CompareValidator to check input is a valid date

The CompareValidator can do more than just compare two controls. You can also compare it against several of the main .net data types such as Date, Integer, Double and Currency.

To do this you would set Operator="DataTypeCheck" and instead of setting the ControlToCompare or ValueToCompare attributes as you normally would you use the Type="Date" (or any of the data types I have listed above).

How To Set a Date Format In GridView Using ASP.NET 2.0

A very common desire is to set a column of a gridview to display just the month, day and year of a DateTime type. The problem is the by default, the HtmlEncode property of the boundfield attribute (
The problem is that if this field is enabled, you can not pass format information to the boundfield control. That is, if you try the following code, you will not get the desired result.

jQuery Date picker Implementation in ASP.NET

I've posted a wrapper ASP.NET around the jQuery.UI Datepickercontrol. This small client side calendar control is compact, looks nice and is very easy to use and I've added it some time back to my control library.

This is primarily an update for the jQuery.ui version, and so I spend a few hours or so cleaning it up which wasn't as easy as it could have been since the API has changed quite drastically from Marc's original implementation. The biggest changes have to do with the theming integration and the resulting explosion of related resources.

If you want to use this component you can check it out a sample and the code here:

How to format datetime & date with century?

Execute the following Microsoft SQL Server T-SQL datetime, date and time formatting scripts in Management Studio Query Editor to demonstrate the usage of the multitude of temporal data formats available and the application of date / datetime functions.


Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions
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