relative dates for the next month-help

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 2005 Forums

Transact-SQL (2005)

relative dates for the next month-help

Author

Topic

midan1
Starting Member

39 Posts

Posted - 2008-04-18 : 07:12:52

need help
i have table employee and the employee insert into table the holidays
the date start >>>> to date end
now i need to create a view only for next moth , in this view i need to see only the relative dates for the next month

"tb_all_holiday before"
date_start date_end
----------------------------------------------------------
15/03/2008 00:00:00 17/09/2008 00:00:00

20/04/2008 00:00:00 12/05/2008 00:00:00

i must to covert it like this

to this - see relative dates for the next month

"VIEW_all_holiday after -next month only "
date_start date_end
----------------------------------------------------------
01/05/2008 00:00:00 31/05/2008 00:00:00

01/05/2008 00:00:00 12/05/2008 00:00:00

TNX for help

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-18 : 07:40:40

it will be
select date_start = case when date_start < '20080501' then '20080501' else date_start end ,
date_end = case when date_end > '20080531' then '20080531' else date_end end
from tb_all_holiday
where date_start <= '20080531
and date_end >= '20080501

Then it's just a matter of making the start and end dates of the month relative to the current date.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

midan1
Starting Member

39 Posts

Posted - 2008-04-18 : 08:09:11

wow it work tnx for the help

see this it from your code
is ok



select [na],[empid], date_start = case when start_date <  DATEADD(m ,1,GETDATE()-DAY (GETDATE()) + 1 )  then  DATEADD(m ,1,GETDATE()-DAY (GETDATE()) + 1 )  else start_date end ,
date_end = case when end_date > DATEADD(m ,2,GETDATE()-DAY (GETDATE()) + 1)-1 then DATEADD(m ,2,GETDATE()-DAY (GETDATE()) + 1)-1 else end_date end
from testdate
where start_date <=DATEADD(m ,2,GETDATE()-DAY (GETDATE()) + 1)-1
and end_date >= DATEADD(m ,1,GETDATE()-DAY (GETDATE()) + 1 )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources