Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Select records between a date range Expand / Collapse
Author
Message
Posted Friday, January 4, 2008 12:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:01 AM
Points: 42,486, Visits: 35,555
Nisha (1/4/2008)
Ok.. Here's my query:

SELECT * FROM [MyDateTable]
WHERE StartDate >= CONVERT(DATETIME, '02/01/2007')
AND StartDate <= CONVERT(DATETIME, '01/04/2008')

This works fine, Jeff! Which is why I was saying, I used Matt's ''don't convert your date field to string'' and I used your ' operators instead of the BETWEEN'.

Do you think I've gotten the point now? :)


Yes, except that if there's a row with a start time of midnight on the 4th you will get it, but you won't get any other records for the 4th. (eg 2008/01/04 00:01, 2008/01/04 07:51, ...)

If you need all the rows that have startdates on the 4th, then use
StartDate < CONVERT(DATETIME, '01/05/2008')
If you don't need anything from the 4th, then use
StartDate < CONVERT(DATETIME, '01/04/2008')

Datetimes are date and time, and you need to take that into account with your queries.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #438771
Posted Friday, January 4, 2008 1:49 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364
ok tell me exactly what I should do, Jeff.. I'm in trouble here then..!
Post #438789
Posted Friday, January 4, 2008 1:54 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364
Silly me!! Haha! Ok now i get what you guys are trying to say! I didn't pay much attention to that cuz I don't have any records for the 4th!
Silly silly!

Thanks a ton, guys!
This was great!
Post #438791
Posted Friday, January 4, 2008 2:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:01 AM
Points: 42,486, Visits: 35,555
Pleasure. Are you all sorted now?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #438798
Posted Friday, January 4, 2008 2:44 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:11 PM
Points: 475, Visits: 364
Totally sorted out!!
You know most of the times, mistakes are out of pure carlessness or oversight rather than lack of knowledge.. I can't always say that for me cuz I'm only a fresher right out of college, at my first job! Haha.
But even at such a young age I'm subject to a great amount of pressure and I always seem to be too 'hurried' to read things right! Its terrible!

Which is why, being here is such a great boon!

Thanks again, guys!
Post #438820
Posted Friday, January 4, 2008 7:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
Heh... Mom always said, "Never run with scissors"... being in a hurry on code has the same effect on careers.

Anyway, glad you figured it out... and thank for the feedback.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #438927
Posted Monday, January 7, 2008 1:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
OP, when you have time, read this
http://sql-server-performance.com/fk_datetime.asp




Madhivanan

Failing to plan is Planning to fail
Post #439468
Posted Friday, April 18, 2014 11:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 18, 2014 11:28 PM
Points: 1, Visits: 0
declare @DateFrom DateTime = CONVERT(DATETIME, '01/01/2014', 103)
declare @DateTo DateTime = CONVERT(DATETIME, '01/03/2014', 103)
;WITH CTE(dt)
AS
(
Select @DateFrom
Union All
Select DATEADD(d,1,dt)FROM CTE
Where dt<@DateTo
)
select DayName,CONVERT(varchar(50), dt, 103)as Date from (select 'Saturday' as DayName,dt from CTE
where DATENAME(dw,dt)In('Saturday') group by dt
Union
select 'Sunday'as DayName,dt from CTE
where DATENAME(dw,dt)In('Sunday'))as result order by dt

Reference : http://aj-learning.com/blog/get-weekend-date-sql-server-specific-date-range/

Post #1563215
Posted Saturday, April 19, 2014 2:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
ajlearningcom (4/18/2014)
declare @DateFrom DateTime = CONVERT(DATETIME, '01/01/2014', 103)
declare @DateTo DateTime = CONVERT(DATETIME, '01/03/2014', 103)
;WITH CTE(dt)
AS
(
Select @DateFrom
Union All
Select DATEADD(d,1,dt)FROM CTE
Where dt<@DateTo
)
select DayName,CONVERT(varchar(50), dt, 103)as Date from (select 'Saturday' as DayName,dt from CTE
where DATENAME(dw,dt)In('Saturday') group by dt
Union
select 'Sunday'as DayName,dt from CTE
where DATENAME(dw,dt)In('Sunday'))as result order by dt

Reference : http://aj-learning.com/blog/get-weekend-date-sql-server-specific-date-range/



Please read the following article for why you should generally avoid rCTEs that "count" by 1 like yours does. There are several much more efficient ways to do the same thing.
Hidden RBAR: Counting with Recursive CTE's


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1563291
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse