I've looked into the knowledge base but haven't found a solution when the variable is dynamic...
To clarify, an event date is populated from a table and thus will always have the same datetime format (e.g. varchar value 'Nov 22 2010 12:00AM') but the value itself will be different depending on the event date.
My code is currently joining some measurement tables that have specific member discharge dates and relevant provider info into a temp table.
These discharge dates are known as the "BaseEventEpisode" variable in the code and what I was doing previously was converting the datetime to varchar so that I could sort by month (Jan, Feb, etc.) with the code below:
...and CONVERT(nvarchar(3), BaseEventEpisode, 107) --convert datetime to string
in ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')
However, this doesn't lend well to sorting for reports; hence, am trying to sort by integer (and corresponding month) like:
and CONVERT(INT, BaseEventEpisode) --convert datetime to int
IN (1,2,3,4,5,6,7,8,9,10,11,12) --show which month disc event occurred
However, whatever example I've found has used a "hard-coded" datetime where the variable BaseEventEpisode currently is showing...any help on a possible solution is much appreciated!!