Solved

sql server date query

Posted on 2010-11-15
4
387 Views
Last Modified: 2012-05-10
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
Comment
Question by:rondre
4 Comments
 
LVL 1

Expert Comment

by:VBisMe
ID: 34141615
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34141694
this expression will always give you 5pm yesterday

dateadd(hour, datediff(d, 0, getdate())*24-7,0)
0
 
LVL 13

Expert Comment

by:sameer2010
ID: 34141736
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
 

Author Closing Comment

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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now