how to get the result for between two date where date is in varchar

Question

5.00/5 (1 vote)

See more:

i have a database where daily sale is recorded.
now i want to get the client outstanding between two dates.
my date_ column is in varchar.
i m not getting the required result.through this query and when i used between query its showing wrong data.so what is the solution? kindly help

Quote:
SELECT
clientid AS [ID]
,SUM(convert(float, total)) AS [SUM]
FROM
buffalo_milk_sale
WHERE
clientid BETWEEN 'HD001' AND 'HD099'
AND
CONVERT(DateTime, date_, 103) >= CONVERT(DateTime, '01/09/2014', 103)
and CONVERT(DateTime, date_, 103) <= C>
error

Quote:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Posted 22-Sep-14 18:38pm

Shantanu sinha

Add a Solution

Comments

Sergey Alexandrovich Kryukov 23-Sep-14 0:47am

Well, and why, why data is on varchar?
—SA

Shantanu sinha 23-Sep-14 0:48am

it was now how to overcome this situation

[no name] 23-Sep-14 1:10am

you can CAST it
<pre>
cast(date_ as datetime) >= cast('01/09/2014' as DateTime)
and cast(date_ as DateTime) <= C </pre>

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Santhosh Babu Mahimairaj · Answer 1 · 2014-09-22T23:09:00

You may get idea from this example

DECLARE @TempTable As TABLE (Id INT,Dd VARCHAR(30))
INSERT INTO @TempTable (Id,Dd)values (1,'17/08/2014')
INSERT INTO @TempTable (Id,Dd)values (2,'18/08/2014')
INSERT INTO @TempTable (Id,Dd)values (3,'19/08/2014')
INSERT INTO @TempTable (Id,Dd)values (4,'20/08/2014')
INSERT INTO @TempTable (Id,Dd)values (5,'21/08/2014')

SELECT * FROM @TempTable WHERE CONVERT(DATE,Dd,103) BETWEEN  CONVERT(DATE,'19/08/2014',103) AND  CONVERT(DATE,'21/08/2014',103)

anup.bhunia · Answer 2 · 2014-09-22T18:59:00

You should not convert current date to varchar format before comparing, specially when you using >, < operator, rather you should convert your data(here sales-date) to Date format from varchar before comparing.

Check if the default date format of your DB Server is same as the date format you using to store the data in varchar format.

If the date format is different, you need to either change the DB Server default format(if possible) or need to re-construct the date in default format. You could use SUBSTRING() function to extract different part of the date(in varchar format) and concatenate them to build the date in desired format.

Note that this could impact your query efficiency.

Have a look here: MSDN Blogs: How to change Date Format after installing SQL server[^]

Alternatively, you can use: MSDN: SET DATEFORMAT (Transact-SQL)[^]

Gihan Liyanage · Answer 3 · 2014-09-22T18:55:00

Solution 1

Are you sure that your all dates of Date column is having DD/MM/YYYY format ?
Use these two codes and see the error of second one

SQL

SELECT CONVERT(DateTime, '13/12/2014', 103)

SQL

SELECT CONVERT(DateTime, '12/13/2014', 103)

Then you will understand what the problem is

Posted 22-Sep-14 18:55pm

Gihan Liyanage

Updated 22-Sep-14 18:57pm

v3

Comments

Shantanu sinha 23-Sep-14 1:00am

yes all my date is in DD/MM/YYYY

Gihan Liyanage 23-Sep-14 1:04am

Did you run the second query I have provided. It will generate the error

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
This is because you '13' should be less than or equal to 12. Because //''/ is the month field for 103 format. I assume , even one of your date can have wrong format.

Shantanu sinha 23-Sep-14 1:27am

I GOT IT TXS

Gihan Liyanage 23-Sep-14 1:29am

Am I correct ?

Shantanu sinha 23-Sep-14 3:05am

yes

Gihan Liyanage 23-Sep-14 3:10am

If you accept my answer plz press accept button. Then any member knows the problem has solved.. Thanx

how to get the result for between two date where date is in varchar

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0