April 14, 2010 at 11:51 pm
I'm having an issue with trying to have a query run for todays date. I'm not sure how to have it convert the date to today. I've got a query that the application generates but I need it to run for "todays date". The code obviously does it for when you run it but I think I need more of a getdate thing. The code is below. I hope I've explained myself well enough.
/*display the amount of problems that have been completed today*/
Select count(*)
FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN SystemInfo sinfo ON wo.WORKSTATIONID=sinfo.WORKSTATIONID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
WHERE (((wo.COMPLETEDTIME >= 1271253600520) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)))
AND ((wo.COMPLETEDTIME <= 1271339999520) AND (((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL))
AND (wo.COMPLETEDTIME != -1)))) AND wot.THD_WOID=wot.WORKORDERID GROUP BY std.STATUSNAME
order by 1 desc
Thanks
Kristen
Kris
April 14, 2010 at 11:59 pm
which column of yours is a datefilter?
Regards,
Raj
April 15, 2010 at 12:02 am
have your statement look at everything > than....
SELECT [Yesterday] = DATEADD(DAY, -1, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP));
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
April 15, 2010 at 12:37 am
Kris-155042 (4/14/2010)
The code is below. I hope I've explained myself well enough.
Unfortunately you have not , mate 🙁
Some Create table statements, some sample data, which column of yours is bothering you, what is the current issue (explaining w.r.t sample data) and what u expect us to work on (i.e., your desired result w.r.t sample data , probably some visual representation of that)-- These things will make us very easy and work on your request straight away...
Cheers!!
April 15, 2010 at 1:16 am
The problem I have is the "completedtime" column. It's in bigint datatype. So when you run the script in the app it just knows the date format. But I need it to do a getdate()) senario (I think). Everytime the script runs it needs to choose the current date. There is another app that runs this script every 5 minutes therefore needing the current date not a number as there currently is.
Hopefully that explains it better.
Kris
April 15, 2010 at 2:10 am
Do you want to convert a bigint column to datetime (completedtime is in bigint and u want to convert it to datetime and then compare it to getdate() ?? ) ??
Give us some sample rows from your table for that completedcolumn and what u want to compare it with! This will really be helpful! From the values u used in your first post for comparing completedtime, i cannot find a matching date pattern.. hmmm..
Do u want something like CAST (completedtime as datetime) >= getdate() ??
Sorry mate, i am unable to extract the issue from your post.. probably lets wait for the SSC heavy-weights to attend your request!!:-)
Edit : Fixed the IFCodes
April 15, 2010 at 3:16 am
This is how the date is in the db
ID User CreatedDate completedDate
119 695 1266183000929 1267575553839
I want to be able to convert that to todays date
Kris
April 15, 2010 at 4:15 am
I am guessing,
wo.COMPLETEDTIME >= Convert(bigint, convert(timestamp, getdate()))
and ofcourse
wo.COMPLETEDTIME < Convert(bigint, convert(timestamp, getdate() + 1))
---------------------------------------------------------------------------------
April 15, 2010 at 4:31 am
Thanks for that, but I hate to be a pain but can you put it in the script as a whole script so I don't make a mess of it 🙂
Kris
April 18, 2010 at 4:21 pm
Hi,
I've made those changes to the script and now it says there is a syntax error with keyword 'GROUP'. I've pasted below so you can see what I've done.
/*display the amount of problems that have been completed today*/
Select count(*)
FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN SystemInfo sinfo ON wo.WORKSTATIONID=sinfo.WORKSTATIONID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
WHERE (((wo.COMPLETEDTIME >= Convert(bigint, convert(timestamp, getdate())) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)))
AND ((wo.COMPLETEDTIME < Convert(bigint, convert(timestamp, getdate() + 1)) AND (((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL))
AND (wo.COMPLETEDTIME != -1)))) AND wot.THD_WOID=wot.WORKORDERID GROUP BY std.STATUSNAME
order by 1 desc
Kris
April 18, 2010 at 10:07 pm
I am not completely sure 'bout using timestamp in my solution as my understanding of 'timestamp' on SS2K5 looks wrong. But you have also not mentioned 'how' you are storing your date field. It was just a guess that I made. So test it completely to make sure it works fine
As for your syntax error problem is concerned, just formatting and looking at the layout would do,
see if this works.
Select count(*)
FROM WorkOrder_Threaded wot
INNER JOIN WorkOrder wo
ON wot.WORKORDERID=wo.WORKORDERID
LEFT JOIN ModeDefinition mdd
ON wo.MODEID=mdd.MODEID
LEFT JOIN DepartmentDefinition dpt
ON wo.DEPTID=dpt.DEPTID
LEFT JOIN SystemInfo sinfo
ON wo.WORKSTATIONID=sinfo.WORKSTATIONID
LEFT JOIN WorkOrderStates wos
ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td
ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti
ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std
ON wos.STATUSID=std.STATUSID
WHERE (
(
(wo.COMPLETEDTIME >= Convert(bigint, convert(timestamp, getdate()))) AND
(
(wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)
)
) AND
(
(wo.COMPLETEDTIME < Convert(bigint, convert(timestamp, getdate() + 1))) AND
(
(
(wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)
) AND (wo.COMPLETEDTIME != -1)
)
)
) AND wot.THD_WOID=wot.WORKORDERID
GROUP BY std.STATUSNAME
order by 1 desc
---------------------------------------------------------------------------------
April 18, 2010 at 10:32 pm
Nabha (4/15/2010)
I am guessing,
wo.COMPLETEDTIME >= Convert(bigint, convert(timestamp, getdate()))
and ofcourse
wo.COMPLETEDTIME < Convert(bigint, convert(timestamp, getdate() + 1))
Ummmm... I know it's just a guess, Nabha, but the TIMESTAMP data type has absolutely nothing to do with dates or times in SQL Server. The BIGINT values Kris has must be based on something like seconds since 19800101 or something like that.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2010 at 11:08 pm
The data type is bigint and does go down to the seconds. I've run that script and I now get no results.
Sorry to be such a pain but I'm really struggling with this bigint thimg
Kris
April 18, 2010 at 11:26 pm
Jeff Moden (4/18/2010)
Nabha (4/15/2010)
Ummmm... I know it's just a guess, Nabha, but the TIMESTAMP data type has absolutely nothing to do with dates or times in SQL Server. The BIGINT values Kris has must be based on something like seconds since 19800101 or something like that.
Yes, sorry, completely off the track. I dint know about timestamp clearly. And it looks like its down to 'seconds' that you are suggesting. Thanks Jeff.
The data type is bigint and does go down to the seconds. I've run that script and I now get no results.
Sorry to be such a pain but I'm really struggling with this bigint thimg
--------------------------------------------------------------------------------
Thanks,
Kris
---------------------------------------------------------------------------------
April 18, 2010 at 11:35 pm
Kris-155042 (4/18/2010)
The data type is bigint and does go down to the seconds. I've run that script and I now get no results.Sorry to be such a pain but I'm really struggling with this bigint thimg
Select count(*)
FROM WorkOrder_Threaded wot
INNER JOIN WorkOrder wo
ON wot.WORKORDERID=wo.WORKORDERID
LEFT JOIN ModeDefinition mdd
ON wo.MODEID=mdd.MODEID
LEFT JOIN DepartmentDefinition dpt
ON wo.DEPTID=dpt.DEPTID
LEFT JOIN SystemInfo sinfo
ON wo.WORKSTATIONID=sinfo.WORKSTATIONID
LEFT JOIN WorkOrderStates wos
ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td
ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti
ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std
ON wos.STATUSID=std.STATUSID
WHERE (
(
(wo.COMPLETEDTIME >= convert(bigint, datediff(ss, '1970-01-01', getdate())) AND
(
(wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)
)
) AND
(
(wo.COMPLETEDTIME < convert(bigint, datediff(ss, '1970-01-01', getdate())) AND
(
(
(wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)
) AND (wo.COMPLETEDTIME != -1)
)
)
) AND wot.THD_WOID=wot.WORKORDERID
GROUP BY std.STATUSNAME
order by 1 desc
Would this help?
---------------------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply