[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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
0
cnrlzen
Asked:
cnrlzen
  • 8
  • 8
3 Solutions
 
Kevin CrossChief Technology OfficerCommented:
cnrlzen,,

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.
SELECT DATEADD(MM, -13, DATEDIFF(DD, 0, GETDATE())) 
     , 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.

0
 
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))

0
 
Kevin CrossChief Technology OfficerCommented:
What data type is "Desk"."Create Time"?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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))
0
 
cnrlzenAuthor Commented:
it is the "table"."column name"
0
 
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.
0
 
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)})
0
 
cnrlzenAuthor Commented:
also tried:
WHERE ("Desk"."Create Time">={ts 'CONVERT(CHAR(8), DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-13, 0), 112)'})

and

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?
0
 
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.?
0
 
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.
0
 
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.
0
 
cnrlzenAuthor Commented:
WHERE ("HPD:Desk"."Create Time">=DATEADD("m", DATEDIFF("m", #1900-01-01#, DATE())-13, #1900-01-01#))

did not work.
0
 
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".
0
 
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"))
0
 
cnrlzenAuthor Commented:
Thanks for the help.
0
 
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,

Kevin
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now