SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Specific Date and Time Range
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 04/19/2012 :  02:18:38  Show Profile  Reply with Quote
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

311 Posts

Posted - 04/19/2012 :  03:28:51  Show Profile  Reply with Quote
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'

Edited by - Villanuev on 04/19/2012 03:38:37
Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 04/19/2012 :  12:05:51  Show Profile  Reply with Quote
tq sir
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47154 Posts

Posted - 04/19/2012 :  14:24:58  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000