Solved

MINUS 6 HOURS from Current date

Posted on 2009-07-03
7
568 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
  • 4
  • 3
7 Comments
 
LVL 2

Accepted Solution

by:
lppperes earned 250 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
This article describes how to add a user-defined command button to the Windows 7 Explorer toolbar.  In the previous article (http://www.experts-exchange.com/A_2172.html), we saw how to put the Delete button back there where it belongs.  "Delete" is …
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…

685 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