Jet Format() with literal to SQL?

[lameid]

I did something that I thought was clever at the time when I created a Jet/MDB frontend database that I am converting to SQL/ADP.

I have items that are bought or sold at prices that may be specific to a date or an entire month. At the time I decide that anything monthly would use the first day of the month and daily would be the first day of the month. So now, I may have to convert something like 6/14/2006 to 6/1/2006 if the pricing is month related so I can join it to the appropriate pricing. I was running an update query to update a field to
CDate(Format(<DateField>,PricingIndex.FormatStringMask))

Currently formatstringMask has one of two values...
mm/\1/yyyy
mm/dd/yyyy

PricingIndex is joined in to the table that contains quantities and updates a field named rateperiod which I in turn use to join to the rate in other queries.

One thought I had was to replace the query with a procedure that uses a recordset to perform the same update. This of course would be slow, so I am hoping for a better option.

While I added the ability to use any format string, it is a reasonable assumption that I am only interested in the first of the month or the date itself.

Another idea I have is to use a Case statement for the update but wasn't sure how to get to the first of the month (I am sure I'll figure it out using books online).

Any thoughts on which way to go or better ideas?

 

[lameid]

I ended up using the Case, Dateadd with the day function to get the desired result. For anyone who finds this on a short note that the SQL server version of Dateadd does not take a string for the interval parameter (no delimeters around mm in this case).