BAQ - Aged stock using PartLots

[imak (Ian Mak)]

Hi all,

I’ve got a solution already but just wondering if there is a cleaner way to doing this?

I need a query that tells me what stock is aged.
Aged stock = Anything 1+ year old.
I’ve queried the PartLot table and found the FirstRefDate which I will be working off.

First calculated field - Today’s date converted to an int. No issue here.

convert(int,getdate())

Second calculated field - FirstRefDate converted to an int
I get an error if I try convert FirstRefDate to int:

What?!

Instead, if I convert to DateTime format first it works:

Final calculated field - Tag anything over 365 days as “aged”

It works. Just feels… messy. Any tips or is this the best solution?
Ian

[dcamlin (David Camlin)]

Not that its THAT much cleaner… but I think you can compound your converts. Put one inside the other.

[dcamlin (David Camlin)]

Along those same lines… once you get the result you want in situations like this, I always like to see if I can combine it all into (1) calc field…

Try it out… looks like you could:

… unless you need the substeps as available columns, that is.

[knash (Ken Nash)]

Might be better to use the datediff sql function and only use one field.

datediff(day,PartLot.FirstRefDate,getdate())

This returns the number of days between the two dates.

then you can query where the date is greater than 365 days… just a thought.

[imak (Ian Mak)]

Thank you David and Ken for those tips! Will give them a try!