I have a table column in SQL 2012 called transitiontime that contains dates and times in the format 2017-02-02 21:00:34.847. I'm trying to query all results within the last 3 months, which I would think would just require me to look for 2017-02, or something to that effect. However if I leave the hyphen in, the query fails.
Examples:
WHERE transitiontime like '%2017%' <- works but returns all values from this year
WHERE transitiontime like '%2017-02%' <- Does not work at all
WHERE transitiontime like '%2017%02%' <- Works but pulls in anything with 2017 and 02 in it, even if 02 is just in the time.
I would love to get the past 3 months in one query, but right now I'd like to just be able to pull from 1 month.
I'm assuming, since you are using the keyword "like", that the field "transitiontime" is a char oder varchar field?
Change the type of the field to DateTime and use the following query, to get all results for the last three months (applying to SQL):
SELECT * FROM table WHERE transitiontime >= DATEADD(month, -3, GETDATE())