Convert nvarchar to date and Get data between two date

Question

I'm collecting data between two date 01/12/2014 and 31/12/2014 but my sql data type in nvarchar

is my query right?

SELECT * from customer where date >= convert(datetime, '01/12/2014', 105) 
AND  date <= convert(datetime, '31/12/2014', 105)

Result

Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

can any one solve this problem...

[Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) - you should always use the most appropriate data type - that's what they're there for, after all! Fix this problem **first** - use the appropriate data time for a date - like `DATE` or `DATETIME2(n)` — marc_s, May 09 '15 at 13:31

score 3 · Accepted Answer · answered May 09 '15 at 13:33

as I know you must separate different parts of a DATE with "-" not with "/" in format 105. here is an example:

SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy

so you must rewrite your code as:

SELECT * from customer where date >= convert(datetime, '01-12-2014', 105) 
AND  date <= convert(datetime, '31-12-2014', 105)

score 1 · Answer 2 · answered May 09 '15 at 13:35

1

The format your string are in, 'dd/mm/yyyy' is 103, not 105 (which is 'dd-mm-yyyy'). So, simply use the correct format:

SELECT * 
FROM customer 
WHERE [date] >= CONVERT(datetime, '01/12/2014', 103) 
  AND [date] <= CONVERT(datetime, '31/12/2014', 103)

answered May 09 '15 at 13:35

Mureinik

score 0 · Answer 3 · answered May 09 '15 at 13:38

0

If your date type is nvarchar why don't you try like this:

SELECT * FROM customer 
WHERE date >= '01/12/2014'
AND date <= '31/12/2014'

answered May 09 '15 at 13:38

Simon

score 0 · Answer 4 · edited May 09 '15 at 14:29

0

Do we really need to convert?

SELECT * FROM DBO.CUSTOMER 
WHERE CAST([date] AS DATE) >= '01/12/2014' AND
CAST([date] AS DATE) <= '31/12/2014'

edited May 09 '15 at 14:29

answered May 09 '15 at 13:52

Firat Aydin

score 0 · Answer 5 · answered May 09 '15 at 14:33

I suggest you to use this:
(I think converting to varchar is make more sense)

SELECT *
FROM customer
WHERE CONVERT(varchar, [date], 103) BETWEEN '01/12/2014' AND '31/12/2014'

In Date and Time Styles; 103 is for with British/French standard with century (yyyy) like this dd/mm/yyyy.

5 Answers5