Find record between two dates

Question

When I write below query it gives record .

SELECT [srno],[order_no],[order_date],[supplier_name],[item_code],[item_name],[quntity]
FROM [first].[dbo].[Purchase_Order]
WHERE order_date BETWEEN '22/04/2015' AND '4/05/2015'

In this query if I don't add 0 in '4/05/2015' it returns record. But when I add 0 to the date i.e. '04/05/2015' it doesn't give any records.

SELECT [srno],[order_no],[order_date],[supplier_name],[item_code],[item_name],[quntity]
FROM [first].[dbo].[Purchase_Order]
WHERE order_date BETWEEN '22/04/2015' AND '04/05/2015'

You can convert it to date type and specifying format rather than depending on in built conversion. — Rohit Vipin Mathews, May 04 '15 at 05:12
You might find this helpful: https://msdn.microsoft.com/en-us/library/ms191307.aspx — SanD, May 04 '15 at 05:12
possible duplicate of [SQL query to select dates between two dates](http://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates) — Rohit Vipin Mathews, May 04 '15 at 05:13
@Pradnya Use proper column types for your data. Just as you wouldn't store a boolean value in a date column, you shouldn't use nvarchar column to store a date. — Zohar Peled, May 04 '15 at 05:43
**[Bad habits to kick : mis-handling date / range queries](https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries)** — ypercubeᵀᴹ, May 04 '15 at 06:00
**[What do BETWEEN and the devil have in common?](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common)** — ypercubeᵀᴹ, May 04 '15 at 06:00

Dhaval · Answer 1 · 2015-05-04T05:48:53.520

1

It Better to Cast it to date rather than depend on IMPLICIT conversion

SELECT [srno],[order_no],[order_date],[supplier_name],[item_code],
 [item_name],[quntity] FROM [first].[dbo].[Purchase_Order] where 
convert(date,order_date,105) BETWEEN cast('22/04/2015' as Date) AND cast('04/05/2015' as date)

edited May 04 '15 at 05:48

answered May 04 '15 at 05:16

Dhaval

2,341
1
13
16

It gives error "Conversion failed when converting date and/or time from character string." – Pradnya Bolli May 04 '15 at 05:18
1

can you tell me type of ORDER_date and Format..?? – Dhaval May 04 '15 at 05:20
Its datatype is nvarchar and format '22-04-2015 04:24:00PM' – Pradnya Bolli May 04 '15 at 05:21
You order_date '22-04-2015 04:24:00PM' contain Character hence sql cant convert character to date or datetime .. – Dhaval May 04 '15 at 05:35
Conversion failed when converting date and/or time from character string. – Pradnya Bolli May 04 '15 at 05:52
Vary every time because user enter that value – Pradnya Bolli May 04 '15 at 05:54
ohh..!! Than its very Hard to Solve..I personally insist that you should fixed the format of orderdate so you can easily do operation on it. – Dhaval May 04 '15 at 06:00

ughai · Accepted Answer · 2015-05-04T06:05:15.053

1

The reason it's not working because SQL is trying to do a string comparison because both your types are string types, But what you really want to do a date comparison.

You should do something like this. Since you only need date part you can strip off the time and use style 103 for your format dd/mm/yyyy.

WHERE CONVERT(DATETIME,LEFT(order_date,10),103)
BETWEEN CONVERT(DATETIME,'20150422') AND CONVERT(DATETIME,'20150504')

Alternately you can use this as well if your order_date has dates like this 5/4/2015 03:20:24PM

WHERE CONVERT(DATETIME,LEFT(order_Date,CHARINDEX(' ', order_Date) - 1),103) 
BETWEEN CONVERT(DATETIME,'20150422') AND CONVERT(DATETIME,'20150504')

A long term solution is to change your column order_date to DATE/DATETIME

edited May 04 '15 at 06:05

answered May 04 '15 at 05:21

ughai

9,830
3
29
47

I convert it into DATETIME But also it gives error 'Conversion failed when converting date and/or time from character string.' – Pradnya Bolli May 04 '15 at 05:25
what is the output of this `SELECT TOP 1 order_date FROM Purchase_Order WHERE ISDATE(order_date) = 0` – ughai May 04 '15 at 05:26
Its result is 21-04-2015 03:20:24PM – Pradnya Bolli May 04 '15 at 05:28
Its gives error The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. – Pradnya Bolli May 04 '15 at 05:37
Are all your dates in this format `DD-MM-YYYY `. if the format changes between `DD-MM-YYYY` and `MM-DD-YYYY`, the above your will not work – ughai May 04 '15 at 05:43
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/76850/discussion-between-ughai-and-pradnya-bolli). – ughai May 04 '15 at 05:51

Find record between two dates

2 Answers2