Who is online? 119 guests and 0 members
Member login | Become a member
posted 12/27/2009 1:53:58 PM by raghav_khunger
In this blog i will explain how to select records falling between two dates.
Below is the sample script :
DECLARE @MyTable TABLE ( ID INT IDENTITY, [MyColumn] VARCHAR(20) ) INSERT INTO @MyTable SELECT 'Jan 7 2010 7:38PM' UNION ALL SELECT 'Feb 7 2010 7:38PM' UNION ALL SELECT 'Mar 7 2010 7:38PM' UNION ALL SELECT 'Apr 7 2010 7:38PM' UNION ALL SELECT 'May 7 2010 7:38PM' UNION ALL SELECT 'Jun 7 2010 7:38PM' UNION ALL SELECT 'Jul 7 2010 7:38PM' UNION ALL SELECT 'Aug 7 2010 7:38PM' UNION ALL SELECT 'Sep 7 2010 7:38PM' UNION ALL SELECT 'Oct 7 2010 7:38PM' UNION ALL SELECT 'Nov 7 2010 7:38PM' UNION ALL SELECT 'Dec 7 2010 7:38PM' UNION ALL SELECT 'Jan 7 2011 7:38PM' UNION ALL SELECT 'Feb 7 2011 7:38PM' UNION ALL SELECT 'Mar 7 2011 7:38PM' UNION ALL SELECT 'Apr 7 2011 7:38PM' UNION ALL SELECT 'May 7 2011 7:38PM' UNION ALL SELECT 'Jun 7 2011 7:38PM' UNION ALL SELECT 'Jul 7 2011 7:38PM' UNION ALL SELECT 'Aug 7 2011 7:38PM' UNION ALL SELECT 'Sep 7 2011 7:38PM' UNION ALL SELECT 'Oct 7 2011 7:38PM' UNION ALL SELECT 'Nov 7 2011 7:38PM' UNION ALL SELECT 'Dec 7 2011 7:38PM' UNION ALL SELECT 'Jan 7 2012 7:38PM' UNION ALL SELECT 'Feb 7 2012 7:38PM' UNION ALL SELECT 'Mar 7 2012 7:38PM' UNION ALL SELECT 'Apr 7 2012 7:38PM' UNION ALL SELECT 'May 7 2012 7:38PM' UNION ALL SELECT 'Jun 7 2012 7:38PM' UNION ALL SELECT 'Jul 7 2012 7:38PM' UNION ALL SELECT 'Aug 7 2012 7:38PM' UNION ALL SELECT 'Sep 7 2012 7:38PM' UNION ALL SELECT 'Oct 7 2012 7:38PM' UNION ALL SELECT 'Nov 7 2012 7:38PM' UNION ALL SELECT 'Dec 7 2012 7:38PM' UNION ALL SELECT 'Jan 7 2013 7:38PM' UNION ALL SELECT 'Feb 7 2013 7:38PM' UNION ALL SELECT 'Mar 7 2013 7:38PM' UNION ALL SELECT 'Apr 7 2013 7:38PM' UNION ALL SELECT 'May 7 2013 7:38PM' UNION ALL SELECT 'Jun 7 2013 7:38PM' UNION ALL SELECT 'Jul 7 2013 7:38PM' UNION ALL SELECT 'Aug 7 2013 7:38PM' UNION ALL SELECT 'Sep 7 2013 7:38PM' UNION ALL SELECT 'Oct 7 2013 7:38PM' UNION ALL SELECT 'Nov 7 2013 7:38PM' UNION ALL SELECT 'Dec 7 2013 7:38PM' UNION ALL SELECT 'Jan 7 2014 7:38PM' UNION ALL SELECT 'Feb 7 2014 7:38PM' UNION ALL SELECT 'Mar 7 2014 7:38PM' UNION ALL SELECT 'Apr 7 2014 7:38PM' UNION ALL SELECT 'May 7 2014 7:38PM' UNION ALL SELECT 'Jun 7 2014 7:38PM' UNION ALL SELECT 'Jul 7 2014 7:38PM' UNION ALL SELECT 'Aug 7 2014 7:38PM' UNION ALL SELECT 'Sep 7 2014 7:38PM' UNION ALL SELECT 'Oct 7 2014 7:38PM' UNION ALL SELECT 'Nov 7 2014 7:38PM' DECLARE @STARTDATE DATETIME DECLARE @ENDDATE DATETIME SET @STARTDATE='20110601' SET @ENDDATE='20140630' SELECT * FROM @MyTable WHERE cast(floor(cast(CAST([MyColumn] AS DATETIME) as float)) as datetime) >= @StartDate AND cast(floor(cast(CAST([MyColumn] AS DATETIME) as float)) as datetime) < DATEADD(DAY, 0, @EndDate) --OUTPUT --(59 row(s) affected) --ID [MyColumn] ------------- -------------------- --18 Jun 7 2011 7:38PM --19 Jul 7 2011 7:38PM --20 Aug 7 2011 7:38PM --21 Sep 7 2011 7:38PM --22 Oct 7 2011 7:38PM --23 Nov 7 2011 7:38PM --24 Dec 7 2011 7:38PM --25 Jan 7 2012 7:38PM --26 Feb 7 2012 7:38PM --27 Mar 7 2012 7:38PM --28 Apr 7 2012 7:38PM --29 May 7 2012 7:38PM --30 Jun 7 2012 7:38PM --31 Jul 7 2012 7:38PM --32 Aug 7 2012 7:38PM --33 Sep 7 2012 7:38PM --34 Oct 7 2012 7:38PM --35 Nov 7 2012 7:38PM --36 Dec 7 2012 7:38PM --37 Jan 7 2013 7:38PM --38 Feb 7 2013 7:38PM --39 Mar 7 2013 7:38PM --40 Apr 7 2013 7:38PM --41 May 7 2013 7:38PM --42 Jun 7 2013 7:38PM --43 Jul 7 2013 7:38PM --44 Aug 7 2013 7:38PM --45 Sep 7 2013 7:38PM --46 Oct 7 2013 7:38PM --47 Nov 7 2013 7:38PM --48 Dec 7 2013 7:38PM --49 Jan 7 2014 7:38PM --50 Feb 7 2014 7:38PM --51 Mar 7 2014 7:38PM --52 Apr 7 2014 7:38PM --53 May 7 2014 7:38PM --54 Jun 7 2014 7:38PM
raghav_khunger (Member since: 4/19/2009 1:46:52 AM)
View raghav_khunger 's profile
Leave a comment
It's fast, easy and free! Submit articles, get your own blog, ask questions & give answers in the forums, and become a better developer, faster.
enter your email address: