2

Came across this issue in our scripts since the New year.

We have several scripts which look back 1 month from current UTC date. Since the New Year, these scripts return zero results because, I assume, Sql doesn't see month 12 as being minus 1 from month 1.

Is there a way to force sql to see the year as the rotating data it is rather than an incremental line?

**Edit

Apologies, I forgot to add examples.

So, one line reads

Select .... Where ... And month (timevalue) = month (getutcdate ()) -1

This gives zero results despite working correctly until January.

I've changed it temporarily to be = 12 which does return the correct data.

**Edit 2

Yep, thanks for the link and sample code. Works perfectly so far. I guess with our current setup it's not using daytime as I assumed, but instead using the month value as an int?

Moominboy
  • 83
  • 9
  • 5
    Yes, if you use `date(time)` datatypes. It's what they're there for! – HoneyBadger Jan 03 '17 at 13:19
  • 5
    You should show how you are deducting a month & from what, one would not expect any problems with the the built-in DATEADD/DATE functions/types. – Alex K. Jan 03 '17 at 13:20
  • 1
    Sample data (and type), expected results and existing code would help... – JohnHC Jan 03 '17 at 13:47
  • 1
    [How to get first and last day of previous month (with timestamp) in SQL Server](http://stackoverflow.com/questions/11743810/how-to-get-first-and-last-day-of-previous-month-with-timestamp-in-sql-server) ... `dateadd(month,-1,getdate())` = December 3rd, 2016, `dateadd(month,datediff(month,0,getdate())-1,0)` = December 1st, 2016 ..http://rextester.com/ZPFY17123 – SqlZim Jan 03 '17 at 13:53
  • 1
    As has been mentioned, using date/datetime datatypes instead of date part datatypes will do this for you. – Andrew O'Brien Jan 03 '17 at 13:56

1 Answers1

1

Check below, I hope it will help:

SELECT MONTH(DATEADD(MONTH, -1, GETUTCDATE())) AS PreviousMonth

returns:

PreviousMonth
12

So your query should be:

Select .... Where ... And month (timevalue) = MONTH(DATEADD(MONTH, -1, GETUTCDATE()))
Pawel Czapski
  • 1,856
  • 2
  • 16
  • 26