asked on

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.

justinbillig

dateadd( "d", Now, -1 )

that will add (-1) day to today

justinbillig

dateadd( "d", Now, -1 )

should be

dateadd( "d", GetDate( ), -1 )

ASKER CERTIFIED SOLUTION

paelo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

imrancs

it should be

dateadd(day,-1,getdate())

infutech

ASKER

Worked like a charm. Thanks to all for responding.

justinbillig

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