troubleshooting Question

Converting DATETIME TO INT -- when the variable is dynamic

Avatar of karen1974
karen1974Flag for United States of America asked on
Microsoft SQL ServerSQL
7 Comments1 Solution406 ViewsLast Modified:

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!!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros