Solved

Automatic Date/Time in a SQL Server Query

Posted on 2004-10-01
6
244 Views
Last Modified: 2012-06-21
I have a query that selects data from one table and inserts it into another one. Part of the query looks for data in a date range. (Between '9/29/2004 0:1:00' And '9/29/2004 23:59:59')
How can I programatically via VB or Stored Procedure generate the time format for a given time period , say the previous day, and insert it into the query?  I will run this query at 1 AM on the next day. For example want to pull data from the 29th so I run it on the 30th.

Any help is appreciated.
0
Comment
Question by:infutech
[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
6 Comments
 
LVL 15

Expert Comment

by:justinbillig
ID: 12201557
dateadd( "d", Now, -1 )

that will add (-1) day to today
0
 
LVL 15

Expert Comment

by:justinbillig
ID: 12201558
dateadd( "d", Now, -1 )


should be


dateadd( "d", GetDate( ), -1 )
0
 
LVL 9

Accepted Solution

by:
paelo earned 500 total points
ID: 12201596
If this is within a stored procedure, you could use:

BETWEEN CONVERT(datetime,CONVERT(varchar,DATEADD(dd,-1,GETDATE()),101)+' 00:01:00')
 AND CONVERT(datetime,CONVERT(varchar,DATEADD(dd,-1,GETDATE()),101)+' 23:59:59')


This will give you the items between 00:01 (first minute) and 11:59:59 (last minute) for the previous day.

-Paul.
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 10

Expert Comment

by:imrancs
ID: 12201598
it should be

 dateadd(day,-1,getdate())


0
 

Author Comment

by:infutech
ID: 12201646
Worked like a charm. Thanks to all for responding.
0
 
LVL 15

Expert Comment

by:justinbillig
ID: 12201651
Here it is in VB


Private Function GetDateClause()
    ' Variables
    Dim dtmNow
    Dim strStartDate
    Dim strEndDate
    Dim strBetweenClause
   
    ' Get Yesterday
    dtmNow = FormatDateTime(DateAdd("d", -1, Now()), vbShortDate)
   
    ' Set start time clause
    strStartDate = CStr(dtmNow) & " 0:1:00 AM "
   
 
    ' Set our end time clause
    strEndDate = CStr(dtmNow) & " 23:59:59"
   
    ' BUild the between clause
    strBetweenClause = " BETWEEN " & strStartDate & " AND " & strEndDate


    ' Return Value
    GetDateClause = strBetweenClause

End Function
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

733 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