Solved

MINUS 6 HOURS from Current date

Posted on 2009-07-03
7
569 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 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Title # Comments Views Activity
Clean Uninstall of VS Professional 2015 5 62
Problem to packaging 1 127
VS2015 Redefinition errors 4 101
PL SQL Developer 7 89
As more and more people are shifting to the latest .Net frameworks, the windows presentation framework is gaining importance by the day. Many people are now turning to WPF controls to provide a rich user experience. I have been using WPF controls fo…
What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

737 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