get date from checkbox data? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

get date from checkbox data?

Author

Topic

brendita
Starting Member

38 Posts

Posted - 2006-01-08 : 22:01:13

In a table I have 5 bit fields (Monday, Tuesday, Wednesday, Thursday, or Friday). They are connected to checkboxes. I was wondering if there is a way to pull out the date according to the week we are currently in? For example, I want to do something like this:

CASE WHEN Monday = 1 THEN (get the date of the current week)
CASE WHEN Tuesday = 1 THEN ...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-08 : 22:18:57

This function will give the first day of week on or before a certain day.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

For example, this will give you the first Monday, on or before today:

select dbo.F_START_OF_WEEK(getdate(),2)	-- Returns Date for Monday

This will give you the first Friday, on or before today:

select dbo.F_START_OF_WEEK(getdate(),6)	-- Returns Date for Friday

CODO ERGO SUM

brendita
Starting Member

38 Posts

Posted - 2006-01-09 : 17:57:48

I try this:

select dbo.F_START_OF_WEEK(getdate(),3)

and I get 1/3/2006. That is LAST Tuesday. Why doesn't it pull for this week?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-09 : 20:25:23

It returns the first Tuesday on or before the date you passed to it, just like I said.

What exactly is it that you want it to do?

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-09 : 22:11:07

You can use dateadd(day, @wday - datepart(weekday, getdate()), getdate()) to get what you want.
@wday is the day that you want. 1 - Monday, 2 - Tuesday , . . . 7 - Sunday. You may have to change your CASE WHEN Monday = 1 a bit to suite the code.

The codes below lists all 7 days of the week

declare
	@date		datetime,
	@weekday	int

SET DATEFIRST 1	-- Monday = 1, Tuesday = 2 etc

select	@date		= dateadd(day, 0, datediff(day, 0, getdate())) -- this will strip off the time portion
select	@weekday	= 7

select 	@date as input_date, wday, dateadd(day, wday - datepart(weekday, @date), @date) as output_date
from
(
	select	1 as wday	union all
	select	2		union all
	select  3		union all
	select  4		union all
	select	5		union all
	select	6		union all
	select	7
) wd

-----------------
'KH'

brendita
Starting Member

38 Posts

Posted - 2006-01-12 : 16:57:10

This works PERFECT!

dateadd(day, @wday - datepart(weekday, getdate()), getdate())

Thanks khtan!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources