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 2008 Forums
 Transact-SQL (2008)
 Specific Date and Time Range

Author  Topic 

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-04-19 : 02:18:38
My table and data as following,

declare @t1 table (idx int, paymentDte datetime);
insert into @t1 values(1,GETDATE())
insert into @t1 values(2,DATEADD(HH,1,GETDATE()))
insert into @t1 values(3,DATEADD(HH,2,GETDATE()))
insert into @t1 values(4,DATEADD(HH,3,GETDATE()))
insert into @t1 values(5,DATEADD(HH,4,GETDATE()))
insert into @t1 values(6,DATEADD(MI,10,GETDATE()))
insert into @t1 values(7,DATEADD(MI,8,GETDATE()))
insert into @t1 values(8,DATEADD(MI,35,GETDATE()))


I'm trying to query a specific range of date and time:
• Date: 20120410 to 20120419
• Time: 10:15 AM to 3:30 PM

So far my query as following,
select * from @t1 where 
--date
CONVERT(VARCHAR(8), paymentDte, 112) > '20120410' and CONVERT(VARCHAR(8), paymentDte, 112) <= '20120419'
and
--hour
DATEPART(HH,paymentDte) >= ? AND DATEPART(hh,paymentDte) <= ?
and
--minute
DATEPART(MI,paymentDte) >= ? AND DATEPART(MI,paymentDte) <= ?


Looking for help from expert

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-04-19 : 03:28:51
Please try to use this one from your Query to get the time. hopefully this will help you.


select * from @t1
--date
Where CONVERT(VARCHAR(8), paymentDte, 112) > '20120410' and CONVERT(VARCHAR(8), paymentDte, 112) <= '20120419'
and
--hour
CONVERT(TIME,paymentDte)> '15:35:23.00000' AND CONVERT(TIME,paymentDte) <= '17:35:23.35700'
Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-04-19 : 12:05:51
tq sir
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 14:24:58
why should you convert it to varchar? why not use date value directly in > < conditions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -