Solved

sql server date query

Posted on 2010-11-15
4
392 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 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
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 …

734 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