Solved

MINUS 6 HOURS from Current date

Posted on 2009-07-03
7
566 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
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.

 

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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
C#: need to import an entire CSV file 4 55
Is COM supported from Apache 1 55
Using MS Code on my Mac 6 68
Please explain the parts of these 2 LINQ expressions 3 33
This article surveys and compares options for encoding and decoding base64 data.  It includes source code in C++ as well as examples of how to use standard Windows API functions for these tasks. We'll look at the algorithms — how encoding and decodi…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

803 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