Dates for report

• daniel

  Mr or Mrs. 500

  Points: 581

  More actions

  May 2, 2003 at 1:01 pm

  #80880

  I need to pass SQL to a third party app as a complete string, without the ability to change variables. The results will be data for a report. The requirements for the report are any invoicedate (datetime) within the past six months from the end of last month. I have come up with the following, but would greatly welcome an easier and shorter way in SQL..

  WHERE invoicedate between

  (SELECT Convert(datetime, Convert(varchar, DateAdd(mm, -6, DateAdd(dd, 1 - DatePart (dd, GetDate()), GetDate())), 101), 101))

  and

  (SELECT Convert(datetime,Convert(varchar, DateAdd(dd, 1 - DatePart (dd, GetDate()), GetDate()), 101), 101))

  MISfIT

  
  MISfIT

• mromm

  SSCarpal Tunnel

  Points: 4301

  More actions

  May 2, 2003 at 3:40 pm

  #456079

  WHERE invoicedate BETWEEN

  DATEADD( mm, -6, CAST( CAST( DATEADD( dd, 1-DAY( getdate()), getdate() ) AS CHAR( 11 ) ) AS DATETIME ) )

  AND

  CAST( CAST( DATEADD( dd, 1-DAY( getdate()), getdate() ) AS CHAR( 11 ) ) AS DATETIME )

  Although I would recommend running GETDATE() one time prior to running this statement and using it in the statement as a constant.

  Edited by - mromm on 05/02/2003 3:41:14 PM