Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

sql server date query

I'm trying to create a query that returns whatever the selection criteria where a datefield is the previous day at 5:00 pm.  I can't figure out how to have the static time of 5:00:00 pm appended to the end of a date and want the query to do this dynmically based on the current date and not putting one in.  Is this possible and if so - how?
0
rondre
Asked:
rondre
1 Solution
 
VBisMeCommented:
Try this to change the time component of a DateTime field:

DECLARE @Date as DateTime = '2010-11-16 16:30'
SELECT @Date as OrigDate

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), @Date, 103) +  ' 17:00', 103) as ResultDate
0
 
cyberkiwiCommented:
this expression will always give you 5pm yesterday

dateadd(hour, datediff(d, 0, getdate())*24-7,0)
0
 
sameer2010Commented:
Try this. It would get previous date and append 5:00 pm to it.
declare @d datetime=getdate()
select cast(cast(dateadd(dd,-1,@d) as varchar(11)) + ' 17:00' as datetime)

Open in new window

0
 
rondreAuthor Commented:
This works great as i'm not always doing this in the management studio and through asp.net code - thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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