Post reply

Is the getDate function the correct function to use?

  • September 10, 2004 at 5:11 pm

    #162622

    Hello,

    I am trying to obtain the values associated with specific dates in a table.  For example, the table in question has a field called "visit_dates" and another field called "subjective".  I need to get a list of the values in the "subjective" field that have a "visit_date" equal to the current date, minus 7 days.  I can't get this to work using the getDate function, as it doesn't accept the argument " -7 ". 

    Does anyone have any suggestions?

    Thanks!

     

     

  • September 10, 2004 at 5:35 pm

    #522658

    sorry for not having a cleaner example but it boils down to using the DATEADD funciton.

     

      CASE WHEN AwardDate = CONVERT(varchar(12), DATEADD(day, -6, GETDATE()), 101) THEN 1

       ELSE 0 END AS AcceptInd,



    Everett Wilson
    ewilson10@yahoo.com

  • September 10, 2004 at 5:56 pm

    #522661

    Hi Everett,

    Thanks for helping me out.  However, I'm afraid I don't completely undertand the query.  For instance, what does the 101 represent?

    Thanks!

  • September 10, 2004 at 6:03 pm

    #522662

    Hello

    The CONVERT function allows one to choose the date format the actual comparison takes place in. 

    101 is the mm/dd/yyyy format

    103 is dd/mm/yyyy

    Look up CONVERT on BOL for the full list.



    Everett Wilson
    ewilson10@yahoo.com

  • September 10, 2004 at 6:06 pm

    #522663

    Ok, Everett, will do. 

    Thanks again!

  • September 11, 2004 at 2:21 pm

    #522676

    Bernard,

         This will also work:

    select convert(varchar, getdate() - 7, 101)

    To add on, depending on the size of the table, I generally refrain from using the functions within the statement.  Each row that is evaluated will have to run through the functions, so if you have a 10000 rows, it will evaluate 10000 times!  Set a variable to the date you are looking for, and use that in the query.

    HTH, Brad

  • September 14, 2004 at 12:22 pm

    #523032

    Many thanks, Brad!

  • September 14, 2004 at 12:33 pm

    #523037

    Hey guys,

    Attached is my query, which still does not return the data that I know exists in visit_date field.

    declare @MinusDate varchar(10)

      select @MinusDate=convert(varchar,getdate() -7,101)

     select deposit_id, sub_obs,visit_date

     from subjective

     where sub_obs like '%morphine sulfate%'

     and visit_date = @MinusDate

    When I run this, I don't get an error message, but rather the column headers with empty fields.  Please help!

    Thanks,

    Bernard

  • September 14, 2004 at 12:36 pm

    #523038

    Is visit_date in the same format as @MinusDate?  I'm thinking if visit_date is a datetime field then you'll need to convert it to a varchar (of type 101).



    Everett Wilson
    ewilson10@yahoo.com

  • September 14, 2004 at 12:40 pm

    #523041

    Hi Everett,

    I did try that, but to no avail.

    declare @MinusDate varchar(10)

      select @MinusDate=convert(varchar,getdate() -7,101)

        select deposit_id, sub_obs, convert(varchar(10),visit_date,101)as VisitDate

     from subjective

     where sub_obs like '%morphine sulfate%'

     and visit_date = @MinusDate

     

     

  • September 14, 2004 at 12:42 pm

    #523044

    Sorry, I was thinking WHERE clause.  Try it there, if it doesn't work then throw the results of the converted visit_date and @MinusDate into the SELECT clasue and let us know how they're coming back.



    Everett Wilson
    ewilson10@yahoo.com

  • September 14, 2004 at 12:48 pm

    #523047

    Try the CONVERSION in the WHERE clause.  Try it there, if it doesn't work then throw the results of the converted visit_date and @MinusDate into the SELECT clasue and let us know how they're coming back.

     

     

    Sorry if this is a doublepost, my browser is giving me inconsistent information.



    Everett Wilson
    ewilson10@yahoo.com

  • September 14, 2004 at 1:03 pm

    #523051

    Hey Everett,

    Excuse my ignorance, but when you say throw the results into the Select clause, I'm not sure I understand. 

    I have already run both as individual Select statements.  In each case, the date is returned properly formatted.  However, I do agree with you that the problem most likely has something to do with the conversion of the visit_date datatype. 

    Any other suggestions, my friend?

    Thanks,

    Bernard

     

  • September 14, 2004 at 1:08 pm

    #523052

    Just curious to see what the actual strings being tested in the WHERE clause are.  This can be seen by removing the date comparison for the moment and adding visit_date, @MinusDate, and convert(varchar(10),visit_date,101) to the SELECT clause in order to see what you're actually getting. 

     

    Feel free to add anything else into the SELECT statement that might be of interest.



    Everett Wilson
    ewilson10@yahoo.com

  • September 14, 2004 at 1:19 pm

    #523056

    Ok, Everett, I did what you suggested. 

    select convert(varchar(10),visit_date,101)as VisitDate, visit_date,@minusdate,deposit_id,sub_obs

    from subjective

     Everything I requested in the Select statement was correctly returned.  So the problem occurs when I attempt the comparison in the the Where clause.

    What can I do?

    Bernard

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply