I had assigned Varchar for date and It's not working when I select between range of dates

Question

Create Table Table_Name([Date] Varchar(100));

Standard 105.

Insert Into Table_Name Values('18-01-2015');
Insert Into Table_Name Values('19-01-2015');
Insert Into Table_Name Values('20-01-2015');
Insert Into Table_Name Values('21-01-2015');
Insert Into Table_Name Values('22-02-2015');
Insert Into Table_Name Values('22-03-2015');
Insert Into Table_Name Values('22-04-2015');
Insert Into Table_Name Values('22-05-2015');

Select [Date] From Table_Name Where [Date] >= '18-01-2015' And [Date] <= '20-01-2015'
Select [Date] From Table_Name Where [Date] Between '18-01-2015' And  '22-01-2015'

Result is 

Date
18-01-2015
19-01-2015
20-01-2015
21-01-2015
22-02-2015
22-03-2015
22-04-2015
22-05-2015

This is my C# Code that how I get date.!

label17.Text = DateTime.Now.ToString("dd-MM-yyyy"); If I try to insert it Date or Datetime datatype., the following error throws., Conversion failed when converting date and/or time from character string.

This is why I am supposed to use Varchar.

This is the query which I want.

SELECT * FROM Sundar_Tyre_Sale_Billing WHERE CONVERT(Date, [Date], 105) BETWEEN CONVERT(Date, '10-02-2015', 105) AND CONVERT(Date, '12-02-2015', 105) Order By [Date] ASC

Thanks everyone..!!

You need to put your values into **single quotes** when inserting! — marc_s, Jun 14 '15 at 10:08
Yeah done. I had completed the insert operation. The values got inserted. — Karthic, Jun 14 '15 at 10:18
Storing dates as varchar is a really bad idea and will never work correctly and you should also always use YYYYMMDD format since that works with all regional settings -- and also reserving varchar(100) for something that will never be 100 characters is also a bad habit. — James Z, Jun 14 '15 at 10:24
I didn't expect the requirement between range of dates. Sorry JamesZ. I thought of casting or convert. It also didn't worked well. — Karthic, Jun 14 '15 at 10:34
[Bad habits to kick : mis-handling date / range queries](https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries) - you should **not** store dates as strings - you're just asking for trouble that way. Use the `DATE` or `DATETIME` datatypes! — marc_s, Jun 14 '15 at 10:43
How to change column datatype in SQL Server database without losing data Any Alter Column possible. Please advice — Karthic, Jun 14 '15 at 10:53

score 0 · Answer 1 · answered Jun 14 '15 at 10:47

0

Create Table Table_Name([Date] Varchar(100));

Standard 105.

Insert Into Table_Name Values('18-01-2015');
Insert Into Table_Name Values('19-01-2015');
Insert Into Table_Name Values('20-01-2015');
Insert Into Table_Name Values('21-01-2015');
Insert Into Table_Name Values('22-02-2015');
Insert Into Table_Name Values('22-03-2015');
Insert Into Table_Name Values('22-04-2015');
Insert Into Table_Name Values('22-05-2015');

Select [Date] From Table_Name Where [Date] >= '18-01-2015' And [Date] <= '20-01-2015'
Select [Date] From Table_Name Where [Date] Between '18-01-2015' And  '22-01-2015'

Result is 

Date
18-01-2015
19-01-2015
20-01-2015
21-01-2015
22-02-2015
22-03-2015
22-04-2015
22-05-2015

answered Jun 14 '15 at 10:47

Karthic

41
1
8

This will be a clear picture of what my problem is ..!! – Karthic Jun 14 '15 at 10:48
Instead of posting an answer, please edit your question to include the information you want. – Zohar Peled Jun 14 '15 at 10:57
This is my C# Code that how I get date.! label17.Text = DateTime.Now.ToString("dd-MM-yyyy"); If I try to insert it Date or Datetime datatype., the following error throws., Conversion failed when converting date and/or time from character string. – Karthic Jun 14 '15 at 12:30

score 0 · Accepted Answer · edited May 23 '17 at 11:51

0

The reason that they are not working is because you use the wrong data type in the first place.
Never use Varchar to store date or values.
Always use the appropriate data type for your data (in this case, Date seems to be what you are looking for, assuming your sql version is 2008 or higher. if it's 2005 or lower, you should either upgrade your sql server to a supported version, or use DateTime).

Also, when specifying dates as string literals, always use ANSI-SQL format (yyyy-mm-dd), since it will never be ambiguous (is 02/04/15 April 2nd or February 4th?)

Another thing, Date is a reserved word (as already stated). Avoid using reserved words and it will save you a lot of time and work. Here is my favorite method to avoid them.

This is what I would do:

CREATE TABLE Tbl_TableName(TableName_Date Date);

INSERT INTO Tbl_TableName VALUES
('2015-01-18'),
('2015-01-19'),
('2015-01-20'),
('2015-01-21'),
('2015-01-22')

Select Date From Table_Name Where Date >= '2015-01-18' And Date <= '2015-01-20'

Select Date From Table_Name Where Date Between '2015-01-18' And  '2015-01-20'

edited May 23 '17 at 11:51

Community

1
1

answered Jun 14 '15 at 10:52

Zohar Peled

79,642
10
69
121

Already there is lot of date stored in this format. I accept the mistake is mine. Plz advice me how to get out of it. Thanks in advance. – Karthic Jun 14 '15 at 10:57
[Read this answer I gave to someone else in your situation](http://stackoverflow.com/questions/30559976/comparing-dates-stored-as-varchar/30569396#30569396) – Zohar Peled Jun 14 '15 at 10:59
This is my C# Code that how I get date.! label17.Text = DateTime.Now.ToString("dd-MM-yyyy"); If I try to insert it Date or Datetime datatype., the following error throws., Conversion failed when converting date and/or time from character string – Karthic Jun 14 '15 at 12:33
SELECT * FROM Sundar_Tyre_Sale_Billing WHERE CAST([Date] AS DATE) BETWEEN CAST('10-02-2015' AS DATE) AND CAST('12-02-2015' AS DATE) Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string. Can you please correct the mistake.! – Karthic Jun 14 '15 at 12:41
Did you read the part of my answer where I wrote **"always use ANSI-SQL format (yyyy-mm-dd)"**? – Zohar Peled Jun 14 '15 at 12:44
Zohar Peled ., Thank you so much. This is the query which I want. SELECT * FROM Sundar_Tyre_Sale_Billing WHERE CONVERT(Date, [Date], 105) BETWEEN CONVERT(Date, '10-02-2015', 105) AND CONVERT(Date, '12-02-2015', 105) Order By [Date] ASC – Karthic Jun 14 '15 at 17:00

I had assigned Varchar for date and It's not working when I select between range of dates

2 Answers2