• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

Automatic Date/Time in a SQL Server Query

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
infutech
Asked:
infutech
1 Solution
 
justinbilligCommented:
dateadd( "d", Now, -1 )

that will add (-1) day to today
0
 
justinbilligCommented:
dateadd( "d", Now, -1 )


should be


dateadd( "d", GetDate( ), -1 )
0
 
paeloCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
imrancsCommented:
it should be

 dateadd(day,-1,getdate())


0
 
infutechAuthor Commented:
Worked like a charm. Thanks to all for responding.
0
 
justinbilligCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now