?
Solved

C# SQL Date range query

Posted on 2011-02-10
10
Medium Priority
?
597 Views
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

0
Comment
Question by:rwheeler23
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 10

Expert Comment

by:Mathiyazhagan
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
0
 

Author Comment

by:rwheeler23
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.
0
 

Author Comment

by:rwheeler23
ID: 34870626
This seems to to the trick!

 "convert(char(8),dmAppt.ApptDateTime,112)>=convert(char(8),convert(datetime,'" + StartDate + "'),112) and " +
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 50

Expert Comment

by:Lowfatspread
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
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
 

Author Comment

by:rwheeler23
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.
0
 

Author Comment

by:rwheeler23
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.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 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

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
 

Author Comment

by:rwheeler23
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.
0
 

Author Comment

by:rwheeler23
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 HH:MM:SS.ss
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 2000 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.
0

Featured Post

Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

764 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