I have a client that collects data from a manufacturing facility a one minute intervals. I already have sql statments to convert the 1 minute data to other timeframes (e.g. 30 min, 60 min, daily). However, now the client wants to look at data converted to irregular time intervals. For example, instead of looking at the first, second, third, etc. 60 minutes of a day, they wish to see data grouped irregularly: first 30 minutes, next 1 hr & 45 min, next 2 hours, next 1 hr & 30 min, etc. These irregular intervals could change; they may later want to look at the first hr, next 2 1/2 hrs, next 1/2 hour, etc.; or whatever strikes their fancy.
So far, all I've come up with is run one query for each desired time session and then do a join on all the resulting tables. Anybody have a better idea on how to do this?
Ok, I know that there is a very smart programmer out there that can resovle my issue.
I am trying to calculate time worked by 15 minute intervals.
Example: Emp 1 started work at 13:00:00 and worked 183 minutes Emp 2 started work at 17:15:00 and worked 150 minutes Emp 3 started work at 08:30:00 and worked 17 minutes
how can I get the time differences between them.Let's say , a person who click on break @ 12:00 PM and he is back and select I am back option @ 12:15 the total break time is 15 minutes. However, can I display this difference of break time.
ID Date Time Length Interval_Num 1 10/11/2014 9:00 420 14
Basically, length represents the # of minutes a person is scheduled for. We have 30 minute intervals. Interval_Num = (Length/30). I need the data to show each interval. For the above example it should only be 14 intervals.For the above example the solution should look like....
ID Date Time Length 1 10/11/2014 9:00 30 1 10/11/2014 9:30 30 1 10/11/2014 10:00 30 1 10/11/2014 10:30 30
I have two fields ID and Log data and log data is a 96 character long string of numbers representing 15 minute intervals from midnight to midnight.
I need to convert these 96 characters to a full 1440 characters which would mean taking each of the 96 characters one by one and making 1 character into 15.
I had Vb macro to do the conversion but now it's broken and I can't fix it. Getting it done in SQL would solve a lot of problems.
I then go from the 1440 fields and do log analysis like total time doing a specific activity but my query is dependent on having all 1440 characters.
I have posted this question in another forum but no one has so far been able to provide a solution to the problem. Since I know Database Journal always has very informative and enlightening posts, I figured I'd post the question here in hopes that some guru can provide an answer.
If you're running SQL 2000 and have any jobs that have been executed, you could perform a query as such:
select last_run_time from msdb.dbo.sysjobsteps
and receive returned values that contain the last time a job was executed "stored in integer datatype" columns. See -> sp_help sysjobsteps.
In SQL 2005 I believe the concept is the same. I think the intent of Microsoft had it mind for doing this was to store the date separate from the time values which won't work using the datetime datatype and I have read this in documentation in the past.
The challenge is to convert that data into a humanly legible 12 or 24 hour time format like 11:00 AM or 02:45:39.
Does anyone have any suggestions or clues to assist in resolving this problem??? :(
I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.
I have a web application developed in VS.NET 2005 [using C# as code behind]; and it uses SQL Server 2000 Enterprise edition as backend. The application runs fine, though it gives an error on IRREGULAR intervals on SQL data requests. Error Details: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
My problem is this: 1) Why does it show an error of SQL 2005, while I use SQL 2000. SQL 2005 is not even installed on the server, though VS.NET 2005 is installed. 2) The error comes only at irregular intervals. Users are able to login properly otherwise. 3) Application starts working again if we do either of the following: (a) Restart IIS (b) Restart application pool (c) Restart server. 4) Named pipes and TCP/IP are added to the "Network Configuration" of the SQL server 2000. 5) Error does not come on any specific page; or any specific code; or at any specific time. 6) We do not have any background service or any other activity happening on the server; and the server hosts only this application, within a single virtual root.
Any thoughts on why is this happening, and how to resolve this?
Need ur help on this...how can I change the datetime stamp to GMT in a table, it's a SQL Server 2005 database. The timestamps are EST,PST,CT... so have to change the arrival and departure times to GMT time zone
I have a DB that gets telephone call information from a telephone switch. We use 3rd part software and a db to capture all of this. However for some reason most of the fields are stored as int DateTime is stored as an int. I am able to convert most of the date field from int by doing select convert(datetime,convert(varchar(8), localday)) that works fine the int are in the db as 20061029 for 10-29-2006. How ever I can't convert the columns starttime and endtime. They are also stored as int but They don't seem to store a date. For instance for the date 9/29/2006 the start time of one particular call is 1159533056 and the endtime is 1159533264 All i need to get from this is the time since I already have the date from another column. I can't figure out what this data represents. How can I convert this to datetime?
Hopefully this is an easy one. I need a function to call from my page footer that takes the NOW date/time value and converts it from Eastern Standard Time to Mountain Standard Time. (My server is on EST time and my users are all on MST time) Of course the function needs to handle daylight savings as well.
Hiiiii All, I want to convert date n time which is in varchar datatype stored in SQL SERVER 2000, into UK & US current date-time. i.e. in server i have stored indian date n time ,but for UK n US clients i want to show their current date n time , Is this psble in asp.net , please tell me how?
I need to create a sp for sql 2000 to run with Crystal 7. (No I can't upgrade the crystal). The user wants the calendar prompt to be issues when Crystal with the calendar function. The problem is that if I define the sp var to be passed in as a datetime, crystal wants the hh mm ss also. if I issue it as a varchar(10) and convert it in my select it won't bring up the calendar control. Never tried this but I am guessing you can't do a convert on a var you feed from an sp like below
create procedure checkdate @date convert(varchar(10),@date,101) as select * from patient_medication where create_timestamp > @date
In my database times are stored as integers. Therefore if someone arrives a 1am the value is 60, 2am 120 etc. Is there a way to convert these integer values to 1:00, 2:00. I've tried converting to a decimal so if somene arrives at 1:30 it returns 1.5, but I still only get the integer value...
I have a stored procedure, it is accepting a parameter which is in local datetime format. But the front end application is passing UTC date format. How I can convert UTC date time to local date time...
i saw the previous query in which it replaces null values to '-' i also want to do same but i have quantity column which is numeric(38,5) and i have to convert it first to varchar to replace null values of quantity - to '-'
select when quantity is null then cast(quantity as varchar)+ '-' else quantity from saleshistory
Hi All, Is there any function sql server to convert datetime zone. For example : EMEA follows GMT time zone. I want to convert time to other zones which are used by other geos like APAC,IJKK. Is there any utility on SQL server to do that?
i have a sql statement that i created in code and it is sending a query to the database when i dim the variable a datetime variable it says that it cant convert it if i make the variable a varchar it works but it only returns one result when it should be returning about 10
here is the code
Public Function dbDGQSSearch(ByVal BatchID As String, ByVal CreatedBy As Integer, ByVal CreatedFor As Integer, ByVal DateCreatedMod As Integer, ByVal DateCreated As String, ByVal DateCompletedMod As Integer, ByVal DateCompleted As String, ByVal DateStartedMod As Integer, ByVal DateStarted As String, ByVal SearchType As Integer, ByVal Completed As Integer, ByVal PriorityMod As Integer, ByVal Priority As Integer, ByVal RemainingCallsMod As Integer, ByVal RemainingCalls As Integer, ByVal TotalCallsMod As Integer, ByVal TotalCalls As Integer, ByVal Bonus As Integer, ByVal Keyword1 As String, ByVal Keyword2 As String, ByVal Keyword3 As String, ByVal Keyword4 As String, ByVal Keyword5 As String)
Dim strQueSearch As String strQueSearch = "SELECT tlkup_Rep.RepID, tlkup_Rep.PositionID, tlkup_Rep.RepFName, tlkup_Rep.RepLName, tlkup_Rep.RepPassword, tlkup_Rep.RepUserName, tlkup_Rep.RepFName + ' ' + tlkup_Rep.RepLName AS RepName, t_Que.QueID, t_Que.BatchID, t_Que.AdminID, t_Que.Manager, t_Que.BonusID, t_Que.QueCompleted, t_Que.QueDate, t_Que.QueNotes, t_Que.QuePriority, t_Que.QueQuantity, t_Que.QueStartDate, t_Que.Mail, t_Que.QueDateComplete, t_Que.QueTotal FROM t_Que INNER JOIN tlkup_Rep ON t_Que.Manager = tlkup_Rep.RepID AND t_Que.Manager = tlkup_Rep.RepID WHERE BatchID<>'' and BatchID<>'2' and BatchID<>'3' and BatchID<>'4' "
'Creates statement for selecting the add to batch data where the criteria appear If BatchID <> "" Then
strQueSearch = strQueSearch + " and t_Que.BatchID= @BatchID " End If If CreatedBy > 1 Then strQueSearch = strQueSearch + " and t_Que.RepID =@RepID " End If If CreatedFor > 1 Then strQueSearch = strQueSearch + " and t_Que.Manager = @Manager " End If
If DateCreated <> "" Then If DateCreatedMod = 0 Then '> strQueSearch = strQueSearch + " and t_Que.QueDate >@QueDate " ElseIf DateCreatedMod = 1 Then '< strQueSearch = strQueSearch + " and t_Que.QueDate <@QueDate " ElseIf DateCreatedMod = 2 Then '= strQueSearch = strQueSearch + " and t_Que.QueDate =@QueDate " End If End If
If DateCompleted <> "" Then If DateCompletedMod = 0 Then '> strQueSearch = strQueSearch + " and t_Que.QueDateComplete >@QueDateComplete " ElseIf DateCompletedMod = 1 Then '< strQueSearch = strQueSearch + " and t_Que.QueDateComplete <@QueDateComplete and t_Que.QueDateComplete >'1/1/1900' " ElseIf DateCompletedMod = 2 Then '= strQueSearch = strQueSearch + " and t_Que.QueDateComplete =@QueDateComplete " End If End If
If DateStarted <> "" Then If DateStartedMod = 0 Then '> strQueSearch = strQueSearch + " and t_Que.QueStartDate >@QueStartDate " ElseIf DateStartedMod = 1 Then '< strQueSearch = strQueSearch + " and t_Que.QueStartDate <@QueStartDate " ElseIf DateStartedMod = 2 Then '= strQueSearch = strQueSearch + " and t_Que.QueStartDate =@QueStartDate " End If End If
If SearchType = 0 Then 'Both 'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 " ElseIf SearchType = 1 Then 'Mail strQueSearch = strQueSearch + " and t_Que.Mail=1 " ElseIf SearchType = 2 Then 'Phone strQueSearch = strQueSearch + " and t_Que.Mail=0 " End If
If Completed = 0 Then 'Both 'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 " ElseIf Completed = 1 Then 'Yes strQueSearch = strQueSearch + " and t_Que.QueCompleted=1 " ElseIf Completed = 2 Then 'No strQueSearch = strQueSearch + " and t_Que.QueCompleted=0 " End If
If Priority > 0 Then If PriorityMod = 0 Then '> strQueSearch = strQueSearch + " and t_Que.QuePriority >@QuePriority " ElseIf PriorityMod = 1 Then '< strQueSearch = strQueSearch + " and t_Que.QuePriority <@QuePriority " ElseIf PriorityMod = 2 Then '= strQueSearch = strQueSearch + " and t_Que.QuePriority =@QuePriority " End If End If If RemainingCalls > 0 Then If RemainingCallsMod = 0 Then '> strQueSearch = strQueSearch + " and t_Que.QueQuantity >@QueQuantity " ElseIf RemainingCallsMod = 1 Then '< strQueSearch = strQueSearch + " and t_Que.QueQuantity <@QueQuantity " ElseIf RemainingCallsMod = 2 Then '= strQueSearch = strQueSearch + " and t_Que.QueQuantity =@QueQuantity " End If End If
If TotalCalls > 0 Then If TotalCallsMod = 0 Then '> strQueSearch = strQueSearch + " and t_Que.QueTotal >@QueTotal " ElseIf TotalCallsMod = 1 Then '< strQueSearch = strQueSearch + " and t_Que.QueTotal <@QueTotal " ElseIf TotalCallsMod = 2 Then '= strQueSearch = strQueSearch + " and t_Que.QueTotal =@QueTotal " End If End If
If Bonus > 1 Then strQueSearch = strQueSearch + " and t_Que.BonusID =@BonusID " End If
If Keyword1 <> "" Then strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword1+'%' " End If If Keyword2 <> "" Then strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword2+'%' " End If If Keyword3 <> "" Then strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword3+'%' " End If If Keyword4 <> "" Then strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword4+'%' " End If If Keyword5 <> "" Then strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword5+'%' " End If
'makes statement into sqlcommand C.daQueSearch.SelectCommand.CommandText = strQueSearch
I want to convert a datetime type into and speciically formatted time:the table contains this:1899-12-30 10:00:00.000I want to reformat it to appear like this:10:00 AM I want to do this with SQL using a CONVERT of something along that line.I've been able to find all kinds of date and date/time formats, but not time alone in the above format.And suggestions?TIA</chaz>
I have a customer time duration format like "12:15" that means 12 minutes and 15 seconds. I want to convert to be "12.25 " that means 12.25 minutes. Any suggestion please. Thanks a lot.
I have a database field that is just an appointment time but it is stored as a char and it appears to be in military time as well.
For reporting purposes I am creating a query that will use the appointment time and I need it to display in standard time format. I am doing this report in crystal and I tried to use crystal tools but it crystal recognizes it as a string so it won't let me set the time format. Is their a cast or convert or some type of sql function I can use to select the appointment time in standard time format? I have been searching online and just haven't found anything yet.
Precisely, here's what I need:When I run getdate(), I get, for example:August 9 2004 5:17 P.M.I want to turn the date portion into:8/9/2004 format and update one column with itI want to turn 5:17 P.M. into:hhmmss and update another column with it.I've been playing around with datepart, with substr, with you name it,and I'm stumped.Any code samples, other help most appreciated.Thanks,Google Jenny*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
