Begin And End-Date, Format date to number

[qjade]

Hello friends,
I am ateempting to build a query base on automatic date range. This query will be run on the first of every month for data of the past month and a half (i.e. Between '20040816' And '20040930'...if it was run on 10/01/2004)
My two questions are this:
1) How do I construct my Between statement for the begin-date to be the 16th of two months ago and the end-date to be one day ago (I understand that Date()-1 would give me the end-date since I am running this the first of the month).

2) Relating to question one, how then would I convert that date format to a number as the data on my SQL server is of a number type yyyymmdd (i.e. 20040816 is the representation for 08/16/2004).

Thank you so very much for your help ahead of time.

 

[cmmrfrds]

First, I would not use between, but instead use >= and <. That is, yournumber >= var1 and yournumber < var2.

Work with the date on the client to format the number that will be sent to sql server.

I would use something that is more flexible than expecting to always run on the 1st. What about retro runs or system problem that prevent a run on the first.

Use the DateAdd function to get 2 months ago.
DateAdd("m",-2,somedate)

End Var.
Extract month and year from the current date and combine with a 1 to make the first of the current month as a number.
i.e 20041001.

Begin Var.
combine the year and the month from the DateAdd function along with 16 to make the beginning number.
i.e. 20040816. You will need to check for January and February since it will require the prior year.

 

[qjade]

thanks for responding cmmrfrds. I just decided to use a simple formatting to subtract date()-45 for roughly a month and a half ago start-date. I appreciate your reply very much though.

 

[PHV]

You may take a look at the DateSerial function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244