Filter using a datetime field

Question

Post reply

Filter using a datetime field

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 1

What's wrong with comparing it to the result of the function call you used to populate PERIOD?

CONVERT(VARCHAR(6), tableB.INV_SER_DTs, 112)

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sdvoranchik SSCrazy Points: 2800 More actions · Answer 2

Another option if you want to stick with the dates and do math as integers is (YEAR(INV_SER_DT)*100000)+MONTH(INV_SER_DT). By multiplying the year function (2011) by 100000 and then adding the month (05) you should still get 201105.

lgoolsby 86333 Old Hand Points: 387 More actions · Answer 3

opc.three (7/7/2011)
What's wrong with comparing it to the result of the function call you used to populate PERIOD?
CONVERT(VARCHAR(6), tableB.INV_SER_DTs, 112)

This is exactly what I was trying but having issues with... or I thought I was. Seemed in my much longer and complete script there was another minor error which was throwing it off. This works exactly as expected.

Thanks,

Lee

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 4

lgoolsby 86333 (7/7/2011)
opc.three (7/7/2011)
What's wrong with comparing it to the result of the function call you used to populate PERIOD?
CONVERT(VARCHAR(6), tableB.INV_SER_DTs, 112)
This is exactly what I was trying but having issues with... or I thought I was. Seemed in my much longer and complete script there was another minor error which was throwing it off. This works exactly as expected.
Thanks,
Lee

Good news, happy you got it sorted 🙂

As an aside, wrapping a column in a CONVERT function essentially prevents SQL Server from utilizing the index on said column. If performance becomes an issue for you and it's traced back to the column you do have this option:

1) Adding a computed column to tableB, maybe named INV_SER_DTs_112, defined as CONVERT(VARCHAR(6), tableB.INV_SER_DTs, 112)

2) Add an index that contains the column INV_SER_DTs_112 that helps your queries and then JOIN to INV_SER_DTs_112

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato