Avatar of karen1974
karen1974
Flag for United States of America asked on

Converting DATETIME TO INT -- when the variable is dynamic

Hello,

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!!
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
macksm

8/22/2022 - Mon
lludden

If your field is EventDate in that format,

You can get the month with
SELECT Datepart(m,CAST(EventDate as datetime))
karen1974

ASKER
When I replace the variable, I keep seem to be getting:

Conversion failed when converting datetime from character string.

I've just changed:

left(BaseEventEpisode,3) as MonthEvent,
to Datepart(m,CAST(EventDate as datetime)) as MonthEvent,


lludden

What version of SQL are you using?  ''Nov 22 2010 12:00AM' is a valid format at least in SQL 2008
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
macksm

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
macksm

In C++ SDK you can declare date as double. bUt this is only possible with C++. No other language supports double date
karen1974

ASKER
@lludden: Yes, I'm using SQL Server 2008, R2.

@macksm: Thank you for letting me know about this; I wasn't sure it was possible.

I think I will then just keep it sorting as converted datetime to string for the months to preserve the integrity of the variable...unless someone may offer an alternate solution for optimal sorting; thank you.
macksm

Which language you are using. You can format your Date Time in VB many different ways in Use the VB  function Format or Format DateTime. To get MonthName Use Month function, Day For day and year for Year To break up a date time variable to Parts.I kTHinlk THis will be useful to you Because You said You require Datetime as string For some purpose.You can convet Any Date to any format Using the above functionss
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.