Date/Time query assistance

Archived Topics
1

Since we started running our project i have found a small minor problem with some of the SQL coding. I know how I want to fix it, I just dont know the proper commands to do. Thought I would post it here for help.

Currently I have the following type of query:

SELECT count(*) FROM progmode1 WHERE shift =1 and fault1 =1 and mach_sel = 1

The problem is that it will count the previous days information as well. SO I though why not tell it that to check the date as well. I had thought about using the t_stamp field but each time I have tried to put it into the query after the WHERE statement I get either “You can not put that type of transact after a where statement” or “Syntax error”

Here are some of the items I had tried:
rtrim(left(t_stamp,10)) = {root container.current} (the root container.current has the simple query of get current time stamp and used the date format command.)
I also tried putting the rtrim command in its own root container item, but could not get the where clause to work correctly.

The SQL instance that it is looking at is quite large, it contains all the days run time event logs. All I want to do is narrow down the parameters of the query so it will only tally for the current day.

Thanks and have a great day.

2

You have to be careful not to mix and match the expression language inside of a query. Also, realize that you can’t use Transact-SQL inside of a query, thats only for stored procedures.

The best thing to do with queries that you’re having trouble with is to first get the query the way you want it, outside of FactoryPMI (in a Sql Server frontend, for example). Then work on getting FactoryPMI to generate that exact query for you.

This should be easy - you just need to restrict the date, as you thought.

Unfortunately, SQL Server makes this much more verbose than it should be, but the idea is something like:

SELECT count(*) FROM progmode1 WHERE shift=1 AND fault=1 AND mach_sel=1 AND DATEPART(yy, t_stamp) = DATEPART(yy, CURRENT_TIMESTAMP) AND DATEPART(mm, t_stamp) = DATEPART(mm, CURRENT_TIMESTAMP) AND DATEPART(dd, t_stamp) = DATEPART(dd, CURRENT_TIMESTAMP)

Hope this helps,

3

Or you might try:

SELECT count(*) FROM progmode1 WHERE shift =1 and fault1 =1 and mach_sel = 1 and datediff(day,t_stamp, current_timestamp)=0

Datediff provides the difference between two dates in the given units. So, you want values that are 0 days different.

Seemed to work for me.

4

Ah, thats much nicer.

5

Thanks Colby and Carl, you guys there at IA are the greatest. Works quite well.