Click here to Skip to main content
12,303,160 members (76,691 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL Server
Hi

I need a query like below.

I have a table contains two date columns like date from date to
date from                              date to
2012-02-01 00:00:00.000	               2012-09-15 00:00:00.000
2012-09-16 00:00:00.000	               2013-06-15 00:00:00.000

If I give a date, then query should chek date and returns the rows only given date with in the date.

Please help me.
Posted 20-Dec-12 6:56am
Comments
Arunprasath Natarajan 21-Dec-12 4:42am
   
if you have two value with you. then why can't try with "between"

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hi,
Try this,
Declare @inputDate datetime
Set @inputDate = Convert(datetime, '2012-05-01', 120)
select * from dateTimeTable where dateFrom >= @inputDate and dateTo < @inputDate
This is a very simple and easy way to get row with given datetime. There are many other way as well.For more information on Datetime in Sql check this:

http://blog.sqlauthority.com/2012/09/12/sql-server-get-date-and-time-from-current-datetime-sql-in-sixty-seconds-025-video/[^]
http://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/[^]

I hope this will help.
Thanks Smile | :)
  Permalink  
v2
Comments
ntrraorao 20-Dec-12 20:46pm
   
Its not working properly

datefrom 2012-06-16 00:00:00.000
dateto 2012-09-30 00:00:00.000

Declare @inputDate datetime
Set @inputDate = Convert(datetime, '2012-09-30', 120)
select * from dateTimeTable where dateFrom >= @inputDate and dateTo < @inputDate

Thea above query is not returning rows. because its fails first condition.
Sk. Tajbir 21-Dec-12 1:55am
   
Its not failing first condition, it fails 2nd condiation because the given date is equal to dateTo. Just put dateTo <= @inputDate then it will work. :)
ntrraorao 21-Dec-12 4:20am
   
Even, I tried its not working.
Sk. Tajbir 21-Dec-12 4:35am
   
You can break your query to check where it's causing problem. First test the query by using only dateFrom >= @inputDate. Check whether this query returns your row or not. Then check dateTo <= @inputDate and check again.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160530.1 | Last Updated 20 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100