locked
fetching data based on date RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I am very new to writing queries, but have written a few simple ones that worked.  Now, I've been asked to create a much more complex query for use with Windows Live Automation so that we can automatically pull and review data from one of our processes.

    I think I have seen examples on most of the things I need to incorporate into the query, but have not seen anything on fetching data by a date.  Is there functionality to be able to pull data for the last 30 days?  A serial number that includes the date of production is stored in the database.  However, it is listed as MMDDYY, so in a common sort, 121110 and 121111 are intermingled, requiring me to delete the unwanted cells.

    Our intention is to have Windows Automation run the query monthly, only pulling the last months data, and pasting it into a spreadsheet I've got set up that will analyze/summarize the data. 

    Thanks in advance.  Have a good day.

    Tuesday, December 20, 2011 3:21 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello,
    Selecting the last month's data and the last 30 days's data might not be the same thing.
    Use the DATEADD function to subtract from today's date:
    select DATEADD(month,-1,getdate())

select DATEADD(day,-30,getdate()))

    Using the format "MMDDYY" is really not convenient. If you can change it, please do ! At least, use the "MM/DD/YY" which is easily recognized by SQL Server.
    If you have to work with your format, you'll have to stuff two "/" into your string.
    SELECT SUBSTRING(yourdate,1,2) + '/' + SUBSTRING(yourdate,3,2) + '/' + SUBSTRING(yourdate,5,2)

    Finally, convert the newly formed string into a valid date :
    select CONVERT(datetime,yourNewString,1)
    You should end up with a select like this :
     
    SELECT yourdata FROM yourtable
WHERE CONVERT(datetime,SUBSTRING(yourdate,1,2) + '/' + SUBSTRING(yourdate,3,2) + '/' + SUBSTRING(yourdate,5,2),1)
 <= DATEADD(month,-1,getdate())


     
     
     
     
    • Proposed as answer by George E Neville Tuesday, December 20, 2011 4:29 PM
    • Marked as answer by Peja Tao Wednesday, December 28, 2011 7:20 AM
    Tuesday, December 20, 2011 4:12 PM