Solved

C# SQL Date range query

Posted on 2011-02-10
10
548 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 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

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 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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now