Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql server date query

Posted on 2010-11-15
4
Medium Priority
?
395 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

715 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