?
Solved

selecting a data range based on day names

Posted on 2007-10-03
13
Medium Priority
?
203 Views
Last Modified: 2010-08-05
HI There,

I'm wondering how I can get a range of dates and times based on day names? For example how can I select between midday wednesday and midnight sunday?

Sean
0
Comment
Question by:bullrout
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20005170
you will need to create a user defined function that translates the text into a concreate date.
am I right that with midday wednesday, you mean past wednesday 12:00PM ?
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20005230
You can use the 'DAY' format of the date, but i m not sure how you can select a range using it.

SELECT *
FROM your_table_name
WHERE TO_CHAR(your_date_column,'DAY') = 'WEDNESDAY'
OR TO_CHAR(your_date_column,'DAY') = 'SATURDAY'
0
 
LVL 5

Author Comment

by:bullrout
ID: 20005311
Hi angelIII,

Yes that's correct. past midday on wednesday and then before midnight on sunday.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 18

Expert Comment

by:Yveau
ID: 20005681
This one will return all 'wednesdays' for you:

select <YourCol>
from   <YourTable>
where  datename(weekday, <YourCol>) = 'wednesday'

Remains  the 'midday' part ...
Hope this helps ...
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20005700
What do you want when saying
>>between midday wednesday and midnight sunday

between most recent wednesday 12:00 and most recent sunday 0:00 ? (that is the start of the sunday !)
or most recent wednesday 12:00 and most recent sunday 23:59.59 ? (that is the end of the sunday !)



0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20005703
@jinesh_kamdar: TO_CHAR is for Oracle, and does not exist in MS SQL server

>Yes that's correct. past midday on wednesday and then before midnight on sunday.
but WHICH wednesday / sunday?

ie, in the following cases, "now" is, October 03, 2007:
* wednesday , 08:00  
* wednesday , 12:00  
* wednesday , 14:00  

what date/time do you want to "start", in those 3 cases?
please explain with "plain enlgish" rules.


0
 
LVL 5

Author Comment

by:bullrout
ID: 20005753
sorry angelIII:

this one * wednesday , 12:00  
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20005787
I guess I asked wrongly.
let me rephrase the question:

what day/time in the week do you want to "switch" from "previous" wednesday to the "next" wednesday?
0
 
LVL 5

Author Comment

by:bullrout
ID: 20005996
I will give you a little background:

the query is for a voting poll that is only open between wednesday 12.00pm midday and sunday 12:am each week. So each time the poll closes it only re-opens on the following wednesday at midday.

I hope I have explained it a little better than before.

0
 
LVL 5

Author Comment

by:bullrout
ID: 20006261
HI angelIII,


did that make any sense?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20007195
please check the following "loop" to check if the results are what you expect as "logic":

declare @d datetime
declare @w datetime
declare @s datetime

set @d = getdate() - 14

while @d < getdate()
begin
  set @w = dateadd(day, case when datepart(weekday, @d) < 4 then - datepart(weekday, @d) - 3  
        when datepart(weekday, @d) > 4 then 4 - datepart(weekday, @d)
        when datepart(hour, @d) < 12 then - datepart(weekday, @d) - 3    
        else 4 - datepart(weekday, @d) end , @d )
  set @w = dateadd(hour,12,convert(datetime, convert(varchar(10), @w, 120),120))
  set @s = dateadd(day, 4, @w)

  select @d date_value, datename(weekday, @d) date_name,  @w wednesday_start, @s sunday_end

  set @d = dateadd(day, 1, @d)

end


if it is, then, your query goes like this:

declare @d datetime
declare @w datetime
declare @s datetime

set @d = getdate()

set @w = dateadd(day, case when datepart(weekday, @d) < 4 then - datepart(weekday, @d) - 3  
        when datepart(weekday, @d) > 4 then 4 - datepart(weekday, @d)
        when datepart(hour, @d) < 12 then - datepart(weekday, @d) - 3    
        else 4 - datepart(weekday, @d) end , @d )
  set @w = dateadd(hour,12,convert(datetime, convert(varchar(10), @w, 120),120))
  set @s = dateadd(day, 4, @w)

select * from yourtable
where yourdatefield >= @w
  and yourdatefield < @s


0
 
LVL 5

Author Comment

by:bullrout
ID: 20026285
Hi angelIII,

I not getting the results that I expected. The wednesday_start value is a friday and the sunday_end is a tuesday. Can you have a look at it please?


declare @w datetime
declare @s datetime

set @d = getdate() - 14

while @d < getdate()
begin
  set @w = dateadd(day, case when datepart(weekday, @d) < 4 then - datepart(weekday, @d) - 3  
        when datepart(weekday, @d) > 4 then 4 - datepart(weekday, @d)
        when datepart(hour, @d) < 12 then - datepart(weekday, @d) - 3    
        else 4 - datepart(weekday, @d) end , @d )
  set @w = dateadd(hour,12,convert(datetime, convert(varchar(10), @w, 120),120))
  set @s = dateadd(day, 4, @w)

  select @d date_value, datename(weekday, @d) date_name,  @w wednesday_start, @s sunday_end

  set @d = dateadd(day, 1, @d)

end


| date_value                      |     dayname   | wednesday_start             |        sunday_end
2007-09-22 10:27:41.507        Saturday       2007-09-19 12:00:00.000      2007-09-23 12:00:00.000
0
 
LVL 5

Author Comment

by:bullrout
ID: 20055494
Hi angelIII,

You have given me a good foundation to build on.

Thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question