Find the datre of the month where the table was loaded.

[Alfredomulet]

Hello everyone. I need to filter my query based upon the record date (RecordDate = day at the end of the month where the specific table was loaded).

This recorddate can be different depending on the source file, so, for August 2005 I might have a file loaded on August 25th into one table, and another file on August 31st into another table. There is just one load per table per month.

When making joins into these tables to obtain the most recent record, I'm using:

...
WHERE AHC.Recorddate >= DateAdd(month,-1,CAST(Year(GetDate()) as varchar) + '-' + CAST(Month(GetDate()) as varchar) + '-01') 
AND AHC.Recorddate <= DateAdd(day,-1,CAST(Year(GetDate()) as varchar) + '-' + CAST(Month(GetDate()) as varchar) + '-01')
...

so it allways chooses the date of the current month where it was loaded the table.

Is it there a more effective way of doing this?

Cheers,

Alfredo

 

[amrita418]

Your solution seems fine to me.

There are many variations to play around with when it comes to computing the first and last day of a month.
Just in case you want to check it out, here is what I use

select    convert(varchar(10), dateadd(day, -day(getdate()) + 1, getdate()), 101),
          convert(varchar(10), dateadd(month, 1,dateadd(day, -day(getdate()), getdate())), 101)

Since you need prior month first and last days get rid on the dateadd on month.

select 	 convert(varchar(10), dateadd(month, -1, dateadd(day, -day(getdate()) + 1, getdate())), 101),
         convert(varchar(10), dateadd(day, -day(getdate()), getdate()), 101)

Regards,
AA