question

nflami avatar image
nflami asked

How do you prevent SQL server from changing function name due to a column name in my table sharing the name?

I'm using the below as a rolling date filter, but SQL thinks i'm referencing the column in my table that shares the same name "mm", when I want to use it as a function.Is there a way around this?

This is the end result i'm looking for:

BETWEEN DATEADD(m, DATEDIFF(mm, 0, GETDATE()) - 12, 0) AND DATEADD(m, DATEDIF(mm, 0, GETDATE()) - 1, 0) 

SQL changes my filter to this due to my table named "DateFile":

BETWEEN DATEADD(m, DATEDIFF(DateFile.mm, 0, GETDATE()) - 12, 0) AND DATEADD(m, DATEDIFF(DateFile.mm, 0, GETDATE()) - 1, 0) 

We are in process of upgrading to a new server which has brought this issue to light. Unfortunately we have several hundred views using the table "DateFile", so the simple fix of changing the column name isn't a great option. I do have a work around, but was hoping for something as simple as two [brackets]. Thanks in advance!

sqlsql-serverfunctiondatediffdateadd
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

spell it out...

datediff(month, 0, getDate())

0 Likes 0 ·

my table "DateFile" also contains "month", so I end with the same issue. This table I have to use contains a number day/month/year fields that SQL would also recognize as functions.

0 Likes 0 ·

can you use m like you did with dateAdd()?

0 Likes 0 ·

1 Answer

· Write an Answer
nflami avatar image
nflami answered

Appears to do the trick. I'll just have to do some more verification. But you've saved me a lot of time! Thank you very much!!

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.