?
Solved

MINUS 6 HOURS from Current date

Posted on 2009-07-03
7
Medium Priority
?
571 Views
Last Modified: 2012-06-27
On this statement how can I get the records that shows the current date but with 6 hours behind.  Let me give an example I am creating records 7/2/2009 but then I get into midnight  so now the date is 7/3/2009 but the records still being save as 7/2/2009. Now it is 7/3/2009 I want to query all the records that happen on the 7/2/2009 even after midnight.
here what I have a statement
HAVING (dbo.BOX_TRANS.PROD_DATE = CAST(CONVERT (varchar(10), GETDATE(), 112) AS DateTime))

Any Ideas
0
Comment
Question by:teogos
[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
  • 3
7 Comments
 
LVL 2

Accepted Solution

by:
lppperes earned 1000 total points
ID: 24772924
Hi teogos,

Try using DateAdd function, like this:
HAVING (dbo.BOX_TRANS.PROD_DATE = CAST(CONVERT (varchar(10), DateAdd(hh, -6, GETDATE()), 112) AS DateTime))
0
 

Author Comment

by:teogos
ID: 24773266
Not records are showing even so I change the time on the Server to reflect 4:00 AM
0
 
LVL 2

Expert Comment

by:lppperes
ID: 24773594
You can try using this:
HAVING (dbo.BOX_TRANS.PROD_DATE = CAST(CONVERT (varchar(10), DateAdd(d, -1, GETDATE()), 112) AS DateTime))
This query will return always the day before today.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:teogos
ID: 24773658
If I run this query at 7/2/2009 will bring the records from the 7/1/2009,  I do now want this
I want to query on 7/3/2009   at 1:00 AM    while the records are still being saved as 7/2/2009 since its the current day run
0
 
LVL 2

Expert Comment

by:lppperes
ID: 24773809
In this case, my first post should work.
Use this query to see if the date and time of server are right:
SELECT GetDate() AS 'Now', DateAdd(hh, -6, GetDate()) AS 'Minus 6 Hours'
0
 

Author Comment

by:teogos
ID: 24773838
I will give a try tomorrow morning before 6 Am  so see if works
0
 

Author Closing Comment

by:teogos
ID: 31599587
This works, Thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article shows how to make a Windows 7 gadget that extends its U/I with a flyout panel -- a window that pops out next to the gadget.  The example gadget shows several additional techniques:  How to automatically resize a gadget or flyout panel t…
In this article, I will show how to use the Ribbon IDs Tool Window to assign the built-in Office icons to a ribbon button.  This tool will help us to find the OfficeImageId that corresponds to our desired built-in Office icon. The tool is part of…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

777 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