September 10, 2008 at 11:08 pm
Using sql2005, I have a simple table of links and a datetime for each row. How can I find all the links with a datetime in the previous week from when the query is executed (8/31/08 to 9/6/08)?
And also any link in the current week (9/7 to 9/13)?
Any help is greatly appreciatesd, thanks!
September 10, 2008 at 11:54 pm
how about...
declare @startdate datetime, @enddate datetime
SELECT @startdate = dateadd(dd, (-6 - DATEPART (dw, getdate())),DATEADD(dd,DATEDIFF(dd,0,getdate()),0))
, @enddate = DATEADD(dd,DATEDIFF(dd,0,getdate())+ 1 ,0)
print convert(varchar(26), @startdate,121)
select yourcolumn list
from yourtable
where thedatetimecolumn between @startdate and @enddate
since apparently there have been issues with using between and a datetime column ...
select yourcolumn list
from yourtable
where thedatetimecolumn >= @startdate
and thedatetimecolumn <= @enddate
btw the DATEADD(dd,DATEDIFF(dd,0,getdate()),0) is just to cut off the time part of the datetime.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 15, 2008 at 10:07 am
thanks so much! I will try this out. This is for the previous week, correct? How would I also find anything in the current week?
Thanks again for your help. I'm a Crystal Reports developer, and Crystal has these built-in functions called "LastFullWeek" or "CurrentWeek" that you can use for defining a date range, so I'm unsure how to write it out in SQL.
September 15, 2008 at 11:40 pm
it is for a range of dates..
test with this and you'll find its begin and end values.
declare @startdate datetime, @enddate datetime
SELECT @startdate = dateadd(dd, (-6 - DATEPART (dw, getdate())),DATEADD(dd,DATEDIFF(dd,0,getdate()),0))
, @enddate = DATEADD(dd,DATEDIFF(dd,0,getdate())+ 1 ,0)
print convert(varchar(26), @startdate,121)
print convert(varchar(26), @enddate,121)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 17, 2008 at 10:40 pm
thanks so much, I see what it's doing now.
Last question, while this gives me a range of dates I can manipulate, I'm trying to find the number of rows with a date in the previous week from the current day. In other words, if today is wednesday, I'm looking to find the date range of the previous Sunday to Saturday. Then I was going to try to find the current week, so if it's wednesday, just the most recent Sunday, monday, tuesday and wednesday.
Thanks again for your help, like I said, in Crystal, these date range functions are built-in so I've never had to write the sql by hand.
Cheers!
September 17, 2008 at 11:13 pm
SELECT DATEDIFF(wk, 0, GETDATE())
gives you number of full weeks from "zero date"
Adding this number of weeks to "zero date":
SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
gives you beginning of curent week.
To get beginning of previous week you need to reduce number of weeks by 1:
SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE())-1, 0)
"Zero Date" in SQL Server is Monday. If you need Sunday started weeks you need to shift "zero date" to Sunday:
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE())-1, 6)
This is gonna be your "cut off" day. Everything happened after this moment is yours.
_____________
Code for TallyGenerator
September 18, 2008 at 7:10 am
declare @startdate datetime, @enddate datetime
SELECT @startdate = dateadd(dd, (-6 - DATEPART (dw, getdate())),DATEADD(dd,DATEDIFF(dd,0,getdate()),0))
, @enddate = DATEADD(dd,DATEDIFF(dd,0,getdate())+ 1 ,0)
--print convert(varchar(26), @startdate,121)
select DATEADD(dd,DATEDIFF(dd,0,thedatecolumn) ,0) as TheDate, count(*) as N_Rows
from yourtable
where thedatetimecolumn between @startdate and @enddate
group by DATEADD(dd,DATEDIFF(dd,0,thedatecolumn) ,0)
order by TheDate
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 19, 2008 at 6:17 am
I recent worked on a project similar to this......
DECLARE @WeekStart smalldatetime
DECLARE @WeekEnd smalldatetime
SET @WeekStart = (DATEADD(day, DATEDIFF(day, 1, getdate()) / 7 * 7, - 1))
SET @WeekEnd = (DATEADD(day, DATEDIFF(day, 7, getdate() - 1) / 7 * 7 + 7, 5))
SELECT @WeekStart, @WeekEnd
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply