• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

Select specific hour in a day regardles of the current time

Greetings experts,
I want to get a query that shows me current date with 9:00 am time ( regardless of the time of running the query ) .. I mean I want to show the result as ( 2010-03-23 09:00:00 ) regardless of the time that I run the query.
Select {fn now()}  will return the current date and time but I want it to show me the current date with 09:00:00 all the time.
The aim of this query is to calculate minutes passed from 9:00 am to the tmie we run the query .. I mean I want to get a result which says ( 230 minutes or 320 minutes passed from Today's working hours.
Thanks in advance for your supports
Regards
0
npiut
Asked:
npiut
2 Solutions
 
wittysloganCommented:
declare @date datetime
select @date=getdate()
SELECT @Date = CONVERT(varChar(40),@date,107)
      SELECT @Date = @Date + ' 09:00:00'
print @Date
0
 
wittysloganCommented:
and this should work ou the minutes:
declare @date datetime
declare @diff int
select @date=getdate()
SELECT @Date = CONVERT(varChar(40),@date,107)
      SELECT @Date = @Date + ' 09:00:00'
print @Date

select @diff=datediff(mi,@date,getdate())
print @diff
0
 
VacheronCommented:
If you want to use it in SQL query please try this:

select datediff(mi,convert(varchar(10),getdate(),101)+' 9:00:00',getdate())  
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
wittysloganCommented:
that's very nice.  I'd use that instead.
0
 
SharathData EngineerCommented:
try this
SELECT Dateadd(HOUR,9,Dateadd(dd,0,Datediff(dd,0,Convert(DATETIME,Getdate()))))

Open in new window

0
 
npiutAuthor Commented:
THank you very much guys
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now