• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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