Use Date Variable for Rolling Date Query in Excel

I have Excel 2010 and am trying to query a database with a condition that date field used is >= today - 395. So everything > 13 months ago.

When I hard code the date in the query it looks like this:
WHERE ("Desk"."Create Time">={ts '2011-01-01 00:00:00'})

How should I modify the "WHERE" statement so that every time I refresh the query data it is giving me the last 395 days?

Apologies if I haven't covered all info you need, I'm new to MS/SQL Query
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:

First, depending on if I am understanding you correctly, I would go for DATEADD(month, -13, DATEDIFF(day, 0, GETDATE())). The highlighted bit gets your today at midnight, then the rest gets you 13 months ago.

Try it for yourself in SQL Management Studio first. The latter expression is useful if what you really want is the first day of the month 13 months ago, i.e., 2010-09-01 versus 2010-09-05.
     , DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-13, 0)

Open in new window

If you want something more along the parameter side, we can explore that next; however, the query must be visualized in MS Query. If it is too complex, then it won't allow parameters.

cnrlzenAuthor Commented:
Thanks, I prefer your second one. I tried using Dateadd functions but I'm getting unreconized data format error... I have a feeling I might be getting the brackets off... could you take my exact Where statement and rewrite it?

IOW, this is not working:
WHERE ("Desk"."Create Time">=DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-13, 0))

Kevin CrossChief Technology OfficerCommented:
What data type is "Desk"."Create Time"?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Kevin CrossChief Technology OfficerCommented:
Although you were using the ODBC canonical {ts } (timstamp) format before, so I guessed DATETIME. Hmm. You can try to convert it. I used this in Excel to MS SQL often, but I do not yet have Excel 2010 and so maybe it does not pass the DATETIME through in the format the server recognizes by default.

i.e., try this:
WHERE ("Desk"."Create Time">=CONVERT(CHAR(8), DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-13, 0), 112))
cnrlzenAuthor Commented:
it is the "table"."column name"
Kevin CrossChief Technology OfficerCommented:
cnrlzen: I gathered that, sorry. I was asking what data type the column "Create Time" is; see http:#36920520.

Or format code 120, which is more like {ts }:
WHERE ("Desk"."Create Time">=CONVERT(VARCHAR(20), DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-13, 0), 120))

Another alternative is if Excel 2010 is wanting this using the ODBC functions like {ts '2011-01-01 00:00:00'}, then we can try the canonical functions. What version of SQL Server do you have? Mine recognizes as least {fn NOW()}-395.
cnrlzenAuthor Commented:
same error, "expected lexical element not found"

I tried it these 2 ways:
1. WHERE ("HPD:Desk"."Create Time">=CONVERT(CHAR(8), DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-13, 0), 112))
2. WHERE ("HPD:Desk"."Create Time">={CONVERT(CHAR(8), DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-13, 0), 112)})
cnrlzenAuthor Commented:
also tried:
WHERE ("Desk"."Create Time">={ts 'CONVERT(CHAR(8), DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-13, 0), 112)'})


WHERE ("HPD:Desk"."Create Time">={fn 'CONVERT(CHAR(8), DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-13, 0), 112)'})

and without the single quotes both ways. Always the same error.

I don't even know what version of sql server I have, how can I find out?
Kevin CrossChief Technology OfficerCommented:
Do you have access to SQL Management Studio? Are you certain this is Microsoft SQL Server on the backend? i.e., how do you know: are you connecting via ODBC data source, etc.?
Kevin CrossChief Technology OfficerCommented:
For grins, you can try:
WHERE ("HPD:Desk"."Create Time">=DATEADD("m", DATEDIFF("m", #1900-01-01#, DATE())-13, #1900-01-01#))

If it works, you are using MS Access. If so, you may be able to do this using DATESERIAL() also.

WHERE ("HPD:Desk"."Create Time">=DATESERIAL(YEAR(NOW()), MONTH(NOW())-13, 1))

Let me know if that works.
cnrlzenAuthor Commented:
I don't have access to SQL Management Studio, but I can ask around. It's ODBC connection. The reason I assume it's SQL is because in Excel the query definition tab under "Command Type:" has "SQL" greyed out.
cnrlzenAuthor Commented:
WHERE ("HPD:Desk"."Create Time">=DATEADD("m", DATEDIFF("m", #1900-01-01#, DATE())-13, #1900-01-01#))

did not work.
Kevin CrossChief Technology OfficerCommented:
Do you get the same error message?
Although, it is probably not MS Access either. I have seen a few previous questions in the PAQ now with almost identical table name and column and they were to Oracle/MAS90 systems and not SQL. The common thread was they were all using MS Query, so got the "lexical" error you did.

If you look under the Data > Connections > Properties, you will see Connection String -- should be two boxes above Command Type. What comes immediately after "DSN="?

If that does not give it away, take the name of the DSN and go to ODBC Data Source Administrator for Windows. Check the data source with that same name for the "Driver".
cnrlzenAuthor Commented:
I came up with another solution... I just defined a parameter in the query by using a "?":
WHERE ("Desk"."Create Time">=? And "Desk"."Create Time"<?)

Then when it prompts me to choose what the parameter value should be, I reference date cells (A1, B1) in the workbook that are using formulas:
CellA1: =DATEVALUE(TEXT(A2,"mm")-1&"-"&TEXT(A2,"yyyy")-1)
CellB1: =DATEVALUE(TEXT(TODAY(),"mmm-yyyy"))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cnrlzenAuthor Commented:
Thanks for the help.
Kevin CrossChief Technology OfficerCommented:
Good. That is not always an option if the query is complex as I stated in my first post, but glad it was in this case. It will be helpful for you in the future to know what SQL platform is behind the scenes as that may impact syntax used, but for now your problems are solved. :)

Best regards and happy coding,

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.