query plz - 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

General SQL Server Forums

New to SQL Server Programming

query plz

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-05 : 01:52:13

hi,

Please provide me with a query which can find whether currentdatetime is in between two given dates or not..

Thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-05 : 01:55:55

i got it

declare @d1 datetime, @d2 datetime

select @d1 = '7/30/2008' ,@d2 = '11/1/2008'

select

case when getdate() between @d1 and @d2 then 'valid time' else 'abovetime' end status

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 02:04:31

quote:
Originally posted by raky

i got it

declare @d1 datetime, @d2 datetime

select @d1 = '7/30/2008' ,@d2 = '11/1/2008'

select

case when getdate() between @d1 and @d2 then 'valid time' else 'abovetime' end status

you need to strip time part from getdate() else it will return else conditions value if you run this at a time other than midnight.
so use dateadd(dd,datediff(dd,0,getdate(),0) instead of getdate()

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-05 : 03:59:48

I need one more help .

declare @test table ( tid int identity(1,1) , appid int , idate datetime, fit varchar(50), tit varchar(50))
insert into @test
select 20,'2008-11-05 00:00:00.000','08:45 AM','10:45 AM'

i want to write a query which gives me message as 'validtime' when currenttimei.e., getdate() is in between idate,fit, tit ..

ssyssy
Starting Member

2 Posts

Posted - 2008-11-05 : 04:01:24

thank you

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-11-05 : 04:15:01

try this

DECLARE @test TABLE ( tid INT IDENTITY(1,1) , appid INT , idate DATETIME, fit VARCHAR(50), tit VARCHAR(50))
INSERT INTO @test
SELECT 20,'2008-11-05 00:00:00.000','08:45 AM','10:45 AM' 

SELECT *
	, CASE WHEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) = idate
				AND DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()), GETDATE()) BETWEEN CONVERT(DATETIME, fit) AND CONVERT(DATETIME, tit) THEN 'ValidTime'
	ELSE 'InValidTime' END
FROM @test

"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources