Query error on Date condition

[ArnabSengupta]

I am using a in DB query with the Connect-in-DB tool where I am conditioning on a NVarChar date column . The condition is Datediff (month, [Date], getdate())<=14. The date format is in MM/DD/YYYY and when I run this I am getting an error 'Identifier script is too long. Maximum length is 128.\48000=103'.

When I use this query on studio, its running fine

Help is much appreciated 

[mbarone]

what kind of database are you querying (oracle, netezza, sql server, etc.)?

[ArnabSengupta]

Hi @mbarone , I am trying to query into Sql Server

[mbarone]

Hmm...our SQL Server dates are in the format YYYY-MM-DD, and the data type is "date".  What does the date look like in your SQL Server database?  

[ArnabSengupta]

Its a NVARCHAR data type its format is MM/DD/YYYY

[mbarone · Accepted solution]

NVARCHAR is not a date, so the database will not be able to do a date function (DATEDIFF) on it.  Is changing the data type to date in the database an option?  If not, you're going to have to first change the data type to date within your query, then do your date function, then convert it back to the nvarchar data type in the mm/dd/yyyy format.  And do that all within your query.  Best option & best practice is if the field is a date, make it a data type of date.