Is the getDate function the correct function to use?

• BernardS

  SSC Journeyman

  Points: 87

  More actions

  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!

   

   

• ewilson10

  SSCarpal Tunnel

  Points: 4255

  More actions

  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

• BernardS

  SSC Journeyman

  Points: 87

  More actions

  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!

• ewilson10

  SSCarpal Tunnel

  Points: 4255

  More actions

  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

• BernardS

  SSC Journeyman

  Points: 87

  More actions

  September 10, 2004 at 6:06 pm

  #522663

  Ok, Everett, will do. 

  Thanks again!

• Brad St. Germaine

  SSC-Addicted

  Points: 481

  More actions

  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

• BernardS

  SSC Journeyman

  Points: 87

  More actions

  September 14, 2004 at 12:22 pm

  #523032

  Many thanks, Brad!

• BernardS

  SSC Journeyman

  Points: 87

  More actions

  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

• ewilson10

  SSCarpal Tunnel

  Points: 4255

  More actions

  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

• BernardS

  SSC Journeyman

  Points: 87

  More actions

  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

   

   

• ewilson10

  SSCarpal Tunnel

  Points: 4255

  More actions

  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

• ewilson10

  SSCarpal Tunnel

  Points: 4255

  More actions

  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

• BernardS

  SSC Journeyman

  Points: 87

  More actions

  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

   

• ewilson10

  SSCarpal Tunnel

  Points: 4255

  More actions

  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

• BernardS

  SSC Journeyman

  Points: 87

  More actions

  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