How to get data from table through between date range

Question

1.00/5 (1 vote)

See more:

This is my table data i want to get data between two dates from FIR_Date column

FIRNo	FIR_DDR	FIR_Date	State_Code	District_Code	PoliceStation_Code
70	     DDR	22/06/2023	           6	6142	6142022
74	     DDR	22/11/2019	           6	6142	6142022
31	     DDR	22/06/2020	           6	6142	6142032
29	     DDR	22/05/2022	           6	6142	6142005
11	     DDR	22/12/2022	          25	25531	25531036
14	     DDR	22/07/2023	          25	25903	25903025
6	     DDR	22/11/2023	          25	25903	25903044

This is my SQL query

select  HealthAdmin.dbo.tblLogin.UserName As MLR_PMR_Prepared_By_Doctor, HealthAdmin.dbo.TblHealthInstituteDetailMaster.HealthInstituteName,
v.vtTitle+' ' +v.vtName as victim_name,
rtrim(p.PS) as Police_Station_name,d.DISTRICT_NAME as Districtname,st.STATE_NAME as Statename, HealthAdmin.dbo.TblDistrictMaster.DName as Health_Institute_District_Name,
cdTransactionID as MLR_PMR_No,convert(varchar,cdTranactionDate,103) as MLR_PMR_Date, cdManualTransaction as Manual_MLR_PMR_No, convert(varchar,cdManualTransactionDate,103) as Manual_MLR_PMR_Date, 
RIGHT (cdPoliceRequestFIRNo, LEN(cdPoliceRequestFIRNo) - 5

) as FIRNo, CASE WHEN SUBSTRING(cdPoliceRequestFIRNo,1,3)='FIR' THEN 'FIR' else 'DDR' END  as FIR_DDR,  cdDatePoliceRequestFIRNo as FIR_Date,
cd_SCode as State_Code, cd_Dcode as District_Code, cd_PSCode as PoliceStation_Code,
--sub.pdf as subsequentopinion_pdf, 
pdfDetails.pdf from Case_Details

 where cd_SCode is not null and cd_Dcode is not null and cd_PSCode is not null and Case_Details.TransStatusID='04' 
 and cd_SCode not in ( ' ','Se')   and cd_Dcode not in ( ' ')  and cd_PSCode not in ( ' ' )
 --and cd_SCode=06 and cd_Dcode=06142 and cd_PSCode=06142032 
 and LTRIM(RTRIM(cdDatePoliceRequestFIRNo)) BETWEEN '22/04/2018' AND '23/04/2021'

What I have tried:

I want to get data between date range from my table column data.

Posted 16-Jul-24 21:41pm

Nishant.Chauhan80

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2024-07-16T22:24:00

It's not entirely clear from your question, but you appear to be storing your dates as strings. That is the fundamental cause of your problem: you're comparing strings, not dates, and the strings aren't even in a "sortable" format (yyyy-MM-dd).

For example, with a string comparison, a value starting with "2/" is greater than a value starting with "11/", because "2" is greater than "1".

And with the dates in d/M/Y order, "23/01/2001" is going to be "between" the values "22/04/2018" and "24/04/2021".

You need to change your column type to use a proper date type. For example, in SQL Server, use date. You will avoid problems like this, and reduce the amount of storage required by your table at the same time.

As a quick-and-dirty workaround, you could try converting the columns in your query. But the performance will not be great!

SQL

WHERE Convert(date, cdDatePoliceRequestFIRNo, 3) BETWEEN '20180422' and '20210423'

NB: For date literals, always use the unambiguous format yyyyMMdd, with no separators.

OriginalGriff · Accepted Answer · 2024-07-16T23:11:00

To add to what Richard has said, never store dates as strings: as well as the sorting problems he talked about there are other problems which creep in as well:
1) Invalid date entry. If the user enters the date as "7th Feb 2023" then it doesn't compare well at all. If they accidentally enter it as "7.2.2023" Because their finger missed the slash key, that's a problem as well. And it they just enter "Today" then what use is that?
2) Different entry formats. If you have a US user (or computer) then they will enter "2-7-2023" while a European would enter "7-2-2023" and a Japanese user might prefer "2023-02-07". That bad enough, but if they skip the century indicator you get "2-7-23", "7-2-23", "23-2-7" and you cannot tell which date they meant once it hits your DB.

So always store dates as DATE, DATETIME, or DATETIME2 and validate them before they get to the DB while the user is still there to confirm or correct them - it seems like more work but it saves you a huge amount of hassle down the line, and makes comparisons and time spans so much easier!

How to get data from table through between date range

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to get data from table through between date range

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us