C# SQL Date range query

Posted on 2011-02-10
Last Modified: 2013-12-17
I am trying to embed a SQL command in my VS C# 2008 program. Nothing I tried is returning any values. I have tried defining StartDate and EndDates as datetime fileds and string fields. This command works in SQL but not when embedded in my code. What is the correct syntax to correct this?
StartDate = txtStartDate.Text;
                EndDate = txtEndDate.Text;

                SqlConnection DataConnection = GetConnection();

                /* Define record set for query results of inventory items */
                DataDataSet = new System.Data.DataSet();
                DataDataSet.CaseSensitive = false;

                DataCommand = new System.Data.SqlClient.SqlCommand();
                DataCommand.Connection = DataConnection;

                DataCommand.CommandText = "SELECT rtrim(mc.PayerCode) as IDInsurCompany,rtrim(nv.PID) as PID,rtrim(nv.IDPatInsur) as IDPatInsur,rtrim(nv.FirstName) as Firstname,rtrim(nv.LastName) as LastName,rtrim(convert(char(10),nv.DOB,101)) as DOB " +
                "from [dm_asarum_OPX]..[dmpatinsur] nv, [mappingcodes]..[MapCodes] mc " +
                "where nv.IDInsurCompany = mc.navcode " +
                "order by nv.IDPatInsur";

                DataCommand.CommandText = "SELECT  CASE WHEN MapCodes.PayerCode IS NULL THEN '0' ELSE MapCodes.PayerCode END AS PayerCode," +
                                          "CASE WHEN dmProvid.ProvId IS NULL THEN '0' ELSE dmProvid.ProvId END AS Provid," +
                                          "CASE WHEN dmPatInsur.PolicyNumber IS NULL THEN '0' ELSE dmPatInsur.PolicyNumber END AS PolicyNumber," +
                                          "CASE WHEN dmPatientDemo.FirstName IS NULL THEN '' ELSE dmPatientDemo.FirstName END AS FirstName," +
                                          "CASE WHEN dmPatientDemo.MiddleInitial IS NULL THEN '' ELSE dmPatientDemo.MiddleInitial END AS MiddleInitial," +
                                          "CASE WHEN dmPatientDemo.LastName IS NULL THEN '' ELSE dmPatientDemo.LastName END Lastname," +
                                          "CASE WHEN dmPatientDemo.DOB IS NULL THEN '' ELSE convert(char(10),dmPatientDemo.DOB,101) END AS DOB," +
                                          "CASE WHEN dmAppt.ApptDateTime IS NULL THEN '' ELSE convert(char(10),dmAppt.ApptDateTime,101) END AS ApptDate " +
                                          "FROM ((([dm_asarum_OPX]..[dmAppt] dmAppt INNER JOIN [dm_asarum_OPX]..[dmPatientDemo] dmPatientDemo ON dmAppt.PID=dmPatientDemo.PID) " +
                                          "INNER JOIN [dm_asarum_OPX]..[dmPatInsur] dmPatInsur ON dmAppt.PID=dmPatInsur.PID) " +
                                          "INNER JOIN [dm_asarum_OPX]..[dmProvider] dmProvider ON dmAppt.IDApptResourceRendering=dmProvider.IDApptResource) " +
                                          "INNER JOIN [dm_asarum_OPX]..[dmProvId] dmProvId ON dmProvider.IDProvider=dmProvId.IDProvider	" +
                                          "LEFT OUTER JOIN [mappingcodes]..[MapCodes] MapCodes ON dmPatInsur.IDInsurCompany=MapCodes.NavCode " +
                                          "WHERE  dmPatInsur.Inactive=0 AND dmProvId.IDGenProvIDType=13 AND dmPatInsur.IDGenRelationship=1 AND " +
                                          "convert(char(8),dmAppt.ApptDateTime,112)>=convert(char(8),'" + StartDate + "',112) and " +
                                          "convert(char(8),dmAppt.ApptDateTime,112)<=convert(char(8),'" + EndDate + "',112) AND dmAppt.IDApptStatus=1 " +
                                          "order by dmAppt.ApptDateTime,dmPatientDemo.LastName,dmPatientDemo.FirstName,dmPatientDemo.MiddleInitial";

Open in new window

Question by:rwheeler23
  • 6
  • 3
LVL 10

Expert Comment

ID: 34868351
try this:
Keep start date and end date as string field and remove convert function of SQL
 i.e>  change   convert(char(8),'" + StartDate + "',112)     to  StartDate

Author Comment

ID: 34870437
That works but I have to type the dates in as 20110211. So it looks like I have to setup this up to convert the date string to a date and then convert it to the 112 format. I want the users to be able to enter 02/11/2011 or 2/11/2011 or 02/11/11 or 2/11/11. I want my program to handle the date conversions.

Author Comment

ID: 34870626
This seems to to the trick!

 "convert(char(8),dmAppt.ApptDateTime,112)>=convert(char(8),convert(datetime,'" + StartDate + "'),112) and " +
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

LVL 50

Expert Comment

ID: 34877956
but just convert startdate to a date/datetime and dont convert the dmAppt.ApptDateTime column...

by using convert(...dmAppt.ApptDateTime ,112) you are making it difficult for the database to use an


      "dmAppt.ApptDateTime between convert(datetime,'" + StartDate + "',101) and " +                "convert(datetime,'" + EndDate + "',101) AND dmAppt.IDApptStatus=1 " +

"CASE WHEN dmProvid.ProvId IS NULL THEN '0' ELSE dmProvid.ProvId END AS Provid," +
"CASE WHEN dmPatientDemo.DOB IS NULL THEN '' ELSE convert(char(10),dmPatientDemo.DOB,101) END AS DOB," +
"Coalesce(dmprovid.provid,'0') as Provid," +
"Coalesce(convert(char(10),dmPatientDemo.DOB,101),'') as DOB," +

Author Comment

ID: 34885160
I tried your suggestion for the date conversion:

 "dmAppt.ApptDateTime between convert(datetime,'" + StartDate + "',101) and " +                "convert(datetime,'" + EndDate + "',101) AND dmAppt.IDApptStatus=1 " +

and it returns no data. If I put it back to my way the data is there.

Author Comment

ID: 34885277
The problem is the time portion of the date. The user has the ability to enter a range of dates. If they pick the same date, it returns nothing and even if they were to pick 02/10/11 - 02/11/11 they would only get 02/10/11.

The coalesce idea works well however, thanks.
LVL 50

Accepted Solution

Lowfatspread earned 500 total points
ID: 34885983
YOU NEED TO ADD THE TIME TO THE INPUT DATES THEN if your database is storing actual
times , and when you are selecting a date range the time component is unimportant...

what datatype are your vairables startdate/enddate?

you should format themn so that they are iso compatible date times


if you pass the dates in this format as strings in the sql they will be recognised as datetimes
without you specifying any further formating functions...  
    '20110214'  and '20110214'

the above covers the day of the 14th feb 2011  (since sql server only has an accuracy of 3 microseconds
for a datetime....

so code it as (having converted startdate and enddate to strings like above outside of the sql....

 "dmAppt.ApptDateTime between '" + StartDate + "' and '" + EndDate + "' AND dmAppt.IDApptStatus=1 " +

Author Comment

ID: 34887355
First I had them as Datetime fields, then string fields. The database is not mne so I have no control of what is put in there. In SQL they are defined as Datetime. I will format the variables in my code with the begin and end times and that should do it.

Author Comment

ID: 34895928
Is there any sample that takes into account all the possible date combinations. Right now the code works as long as the dates are entered as MM/DD/YYYY. But some months and days have 1 digit and other have two. I just want the user to enter dates and then have code convert to YYYYMMDD
LVL 50

Assisted Solution

Lowfatspread earned 500 total points
ID: 34897644
the safest method for dates is always to have 3 separate fields clearly labeled for the Day , month and year...  getting the user to input leading zeros is usually acceptable but you can add them in code if
you want...  then internally treating them as a char(8) string is best (YYYYMMDD)

(similarly for times ,,,)

convert and store them as Date/Datetime/time datatypes in your database and for datetime arithmetic.

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question