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?
rondreAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
this expression will always give you 5pm yesterday

dateadd(hour, datediff(d, 0, getdate())*24-7,0)
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.