display records between a date range

Reality Strikes

Now since I have got the report working and displaying properly, I'm struck @ this thing and I've got lots of confusion revolving this. I've got a table with 5 fields in which date was splitted in 4 columns as DD, MM, YY1 and YY2. Using dataset (C#.Net), I've added a new column and concatenated these 4 fields into one and made it look like a date field (mm-dd-yyyy) and this field is a String field. Now the actual problem is that I want to display report based on a Date Range Selection from my Form (i.e 2 Textboxes for Start Date and End Date). I've not much clear idea as how to proceed validating the records as it can't be done at the database level since my database doesn't have anything like a Date Field. Any suggestions would be highly appreciated.

Luc Pattyn

Hi, I can see only two clean ways to do date ranges: 1. use a real Date or DateTime field in the database, then SQL Server will be able to do real date checking. 2. use an integer field in the yyyyMMdd format (as I suggested before), so numeric and chronological order are the same, hence you can do numeric comparison. A field that "look like a date field (mm-dd-yyyy)" is of no use to SQL server! :)

Luc Pattyn [Forum Guidelines] [My Articles]

Voting for dummies? No thanks. X|

Reality Strikes

Luc, there is nothing much I can do on the database coz it resides on the AS400 IBM iSeries server and that's the way how dates are stored in mainframe systems. Then regarding the "looking like date field", its infact the the date itself which is stored in a string field and if I'm not wrong that can be converted into integer or date field..........that's wht i think...........am i right............??? and moreover, i need help on the logical part of how to proceed on it.........

Luc Pattyn

if that is the way dates are kept all the time (I do find that hard to believe), then they are bound to have a way to do date comparisons and date range checks based on that format too... I am afraid I can't help you any further, I am no database expert, and never addressed an AS400. :)

Luc Pattyn [Forum Guidelines] [My Articles]

Voting for dummies? No thanks. X|

Reality Strikes

Luc, that's the way they do it in mainframe server. But the thing is that I'm looking for something which could actually help me in doing the date range comparison at the code level. Normally we do it at the database level but since my database doesn't have a date field, I've to do it at the code level. I'm saying so, coz my code concatenates the 4 different fields into one field and display it as date field on crystal report. So if there are chances of any comparison for date range then it has to be done at code level only coz that's the place where its actually shown up as date field. Did i confused you...........???? To be more clear, I want the date range comparison to be done exactly after the code where its concatenating the fields.............and the code for the same is as given below:

for(Int32 i = 0; i < newPDS.Tables[0].Rows.Count; i++)
{
DataRow row = newPDS.Tables[0].Rows[i];

row\["ADM\_DATE"\] = row\["CADMMM"\].ToString() + "/" + row\["CADMDD"\].ToString() + "/" + row\["CADMHH"\].ToString() + row\["CADMYY"\].ToString();

}

Luc Pattyn

Hi, if you have numeric values for year, month and day, you can create a new DateTime() instance with the corresponding value DateTime dt=new DateTime(year, month, day); [ADDED] If all you have is a datelike string, then you can try and extract a DateTime from it using DateTime.Parse(), DateTime.ParseExact() or DateTime.TryParse().[/ADDED] And when you have two DateTime instances you can compare them with the comparison operators such as < and == and > :)

Luc Pattyn [Forum Guidelines] [My Articles]

Voting for dummies? No thanks. X|

modified on Monday, July 28, 2008 2:21 PM

Lost User

I wouldn't fetch all records and do it on client side, because it would unnecessarily create high load on the database server and fetch more records than actually needed. Can't you do something like this on server side?

SELECT ...
FROM table
WHERE YY >= startyear
AND MM >= startmonth
AND DD >= startday
AND YY <= endyear
AND MM <= endmonth
AND DD <= endday

regards

Luc Pattyn

The principle is OK, but the implementation is wrong. There is a huge difference between date1<date2 and year1<year2 AND month1<month2 AND day1<day2 The correct way would be something like this: year1<year2 OR (year1==year2 AND (month1<month2 OR (..etc..)) :)

Luc Pattyn [Forum Guidelines] [My Articles]

Voting for dummies? No thanks. X|

Reality Strikes

I totally agree with what you guys are saying, but please let me know whether I can use the SELECT statement on DataSet the same way as we use on Database tables coz I don't have the datefield in database table, its only the newly created DataSet which is holding the DateField. Let's say, the code goes like this :

DataColumn dCol = new DataColumn(newPDS.Tables[0].Columns.Add("ADM_DATE", typeof(String)).ToString());
for(Int32 i = 0; i < newPDS.Tables[0].Rows.Count; i++)
{
DataRow row = newPDS.Tables[0].Rows[i];

row\["ADM\_DATE"\] = row\["CADMMM"\].ToString() + "/" + row\["CADMDD"\].ToString() + "/" + row\["CADMHH"\].ToString() + row\["CADMYY"\].ToString();

}

Now its the newPDS DataSet, which is having the DateField. So, now please tell me a way to apply the date range thing within this piece of code. (Date range from Form is entered in 2 Textboxes as START_DATE and END_DATE in the format of DD/MM/YYYY. THANKS