Solved

C# SQL Date range query

Posted on 2011-02-10
10
553 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
DevOps Toolchain Recommendations

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

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
C# application error "Parameter Is Not Valid". 3 79
Help writing a query 6 71
Syntax Error 2 44
1 FROM DUAL wont work with additional columns ?? 4 22
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

25 Experts available now in Live!

Get 1:1 Help Now