• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 631
  • Last Modified:

C# SQL Date range query

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

0
rwheeler23
Asked:
rwheeler23
  • 6
  • 3
2 Solutions
 
MathiyazhaganCommented:
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
0
 
rwheeler23Author Commented:
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.
0
 
rwheeler23Author Commented:
This seems to to the trick!

 "convert(char(8),dmAppt.ApptDateTime,112)>=convert(char(8),convert(datetime,'" + StartDate + "'),112) and " +
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LowfatspreadCommented:
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
index....

so

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


also
"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," +
is
"Coalesce(dmprovid.provid,'0') as Provid," +
"Coalesce(convert(char(10),dmPatientDemo.DOB,101),'') as DOB," +
0
 
rwheeler23Author Commented:
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.
0
 
rwheeler23Author Commented:
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.
0
 
LowfatspreadCommented:
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

ie  YYYYMMDD HH:MM:SS.sss

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 00.00.00.000'  and '20110214 23.59.59.997'

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 " +
0
 
rwheeler23Author Commented:
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.
0
 
rwheeler23Author Commented:
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 HH:MM:SS.ss
0
 
LowfatspreadCommented:
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now