0

My design have two textbox and a calendar. I want to write a query to select a date range for example from 19/03/2014 to 03/05/2014. My date is select from the calendar and it will fill in both txtsearch.Text and txtsearch2.Text.

I know the query to get the date between two dates, but how can I proceed my query to get the data range between different dates, months and also years too?

Here is my query to get date between two dates:

SELECT * FROM mytable 
WHERE CONVERT(VARCHAR(10),enq_date_time,103) BETWEEN 
                   CONVERT(VARCHAR(10),'" & txtsearch.Text.Trim & "',103) 
               AND CONVERT(VARCHAR(10),'" & txtsearch2.Text.Trim & "',103)
Aaron Martin
  • 128
  • 1
  • 13
Lavender Dream
  • 1
  • 1
  • 4
  • Er, what? Can we get some sample starting data and desired results? You really want to read [this blog post](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) about `BETWEEN` and continuous-range types, especially when dealing with timestamps on SQL Server. – Clockwork-Muse May 20 '14 at 10:42
  • question have been edited. – Lavender Dream May 21 '14 at 08:48

2 Answers2

1

You can use datediff to calculate either months or years 

select 'Months', datediff(month, '2012-08-23 18:45', '2014-02-01 14:30')
union
select 'Years', datediff(year, '2012-08-23 18:45', '2014-02-01 14:30');

SQLFiddle

Olaf Dietsche
  • 72,253
  • 8
  • 102
  • 198
  • On my design, I have two textbox. I want the query is select a date range for example from 19/03/2014 to 03/05/2014. My date is select from the calendar. Hope you can write the query for me. Thanks – Lavender Dream May 21 '14 at 03:23
  • If you only want the rows for some date range, you can look at @mmhasannn's answer. – Olaf Dietsche May 21 '14 at 12:32
  • After rereading your question, do you want *only* the date range, nothing else? – Olaf Dietsche May 21 '14 at 12:53
  • Yes. Maybe the selection between txtsearch and txtsearch2 might different date and month. So using mmhasannn's answer is correct? – Lavender Dream May 22 '14 at 05:17
  • If you only want the date range between `txtsearch` and `txtsearch2`, then you can calculate this without using SQL at all. Just using C#, PHP or Java would be sufficient. – Olaf Dietsche May 22 '14 at 07:07
  • Can you write out how to code in C# for me? – Lavender Dream May 22 '14 at 07:40
  • You can look at this question http://stackoverflow.com/q/1607336/1741542 or http://www.codegateway.com/2012/01/c-datetime-difference.html or http://msdn.microsoft.com/en-us/library/aa287590%28v=vs.71%29.aspx – Olaf Dietsche May 22 '14 at 09:25
0
"SELECT * FROM mytable 
WHERE YEAR(enq_date_time) BETWEEN " & txtYearInitial.Text.Trim & " AND " & txtYearFinal.Text.Trim;
Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133