Retrive data between two dates in sql server and also from single column or double

Question

0.00/5 (No votes)

See more:

Dear All,

I have two fields of varchar datatype to store the simple date. i know there is not good way to store date in varchar but by some mistake it taken it and lots of data inserted now.

I have 2 column like 1. From date and 2 . To date

if i have data like

From Date | To Date
----------------------------------
1. 2012/08/06 2012/08/06
2. 2012/08/06 2012/08/09
3. 2012/08/05 2012/08/09

if execute the query with 2012/08/06 and 2012/08/06 this dates, the two result i want to come like 1 and 2 because the second one containt the same date. ans same for to date.

so, how can i achive this ..

i use but not getting the perfct result....

select leave_id,date_of_leave,from_name,dept,from_date,to_date,total_days,reason,report_to_whom from leave_record inner join empl on leave_record.from_name=empl.name  where convert(datetime,leave_Record.date_of_leave,103) between '2012/08/06' and '2012/08/09' order by convert(datetime,leave_record.from_date,103) desc

Thanks and Regards,,,
MItesh

Posted 2-Nov-12 1:34am

Mitesh Machhi

Add a Solution

Comments

Santhosh Kumar Jayaraman 2-Nov-12 7:47am

Please be clear, how you want the output?

[no name] 2-Nov-12 7:49am

if i enter the date 6 and if it match with yyyy/mm/dd in both from and to date then it`s data want to display

1 solution

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)

Aarti Meswania · Answer 1 · 2012-11-02T02:13:00

Solution 1

Try this,

SQL

select leave_id,date_of_leave,from_name,dept,from_date,to_date,total_days,reason,report_to_whom
from leave_record
inner join empl on leave_record.from_name=empl.name
where convert(datetime,convert(varchar(10),leave_Record.date_of_leave,102)) between '2012/08/06' and '2012/08/09' order by convert(datetime,convert(varchar(10),leave_record.from_date,102)) desc

Happy Coding!
:)

Posted 2-Nov-12 2:13am

Aarti Meswania

Comments

[no name] 2-Nov-12 8:58am

dear i got an error :The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Aarti Meswania 2-Nov-12 9:02am

please tell me datatype of your field from_date