Select - 4 Hours
May 16, 2006Hi,
I need to make a select getDate() subtracting 4 hours...
How can I make it ???
Thanks
ADVERTISEMENT
Select Records From Database Which Are 6 Hours Old Or Less
Dec 29, 2007 I have a table which has a datetime field, which is auto populated by MSSQLs GETNOW function.
I would like a query that returns results with dates 6 hours old or less
SELECT *
From Story
WHERE datetime LTE 6 hours ago.
Can anyone offer help?
Service Pack Install Causes Insert...into Or Select Into To Take Hours
Jan 3, 2000Hi!
I am totally new to this but have access to a SQL Server (version 6.5) and am looking at the processes used on it to learn. It is being used as a reporting server and have run into a wierd problem:
We discovered that it did not have any service packs at all on it so we applied SP4 to it (Microsoft SQL Server 6.50 - 6.50.281 (Intel X86) ).
Now what took a few minutes takes **hours** to do. In particular, a particular SELECT INTO statement that has a few joins in the WHERE clause is the problem. I changed the code from the SELECT INTO and created the table and used an INSERT...INTO statement. This did not help.
This process has ran successfully on the SQL Server without any service packes for over two years and now with the SP4 applied, it takes forever.
The SQL statement that is used has also been working fine for the same amount of time. One thing that I notice with it is that the join syntax uses *= instead of the more current way (LEFT OUTER JOIN). We are also clearing the logs before running the statement to make sure the space is available. This is run against a header table with around 600,000 rows and a detail table with approx 1,500,000 rows.
Any help would be greatly appreciated!
Scott
Select Rows With Datetime Column Ealier Than 2 Hours Ago?
Apr 5, 2007Hello.
I have a table with a datetime column.
I need to select rows with a NULL or a datetime earlier than 2 hours
ago if getdate() time is currently between 6am and 7pm.
Thanks for any help or information!
Transact SQL :: To Display Days Hours Mins Format Based On Business Hours
Apr 22, 2015I want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) col2 (In Hours : Mins)
3days 4:5
In this first have to add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
Breaking Down Total Hours Worked Into Day And Evening Hours
Sep 21, 2006I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
View 3 Replies View RelatedConverting Decimal Hours To Hours And Minutes
May 13, 2008I have a float variable that holds a decimal number of hours.
So 1.5 equals 1 hour 30 minutes.
I need to change this to the format 1:30
Any idea how to do this?
SQL M.P.s - 1 Day = 24 Hours??? Or 24 - 48 Hours???
Jun 28, 2001We have a few Servers that have space issues that need full DB Backups every day. As a result, we have to carefully watch how many DB Backup files we keep on the Server. On the Maintenance Plan, if I set the 'Delete files older than:' to '1' 'Day', will this delete anything over 24 hours old? What I have noticed, is that it tends to delete on Tuesday anything that was created on Sunday, but not Monday. Therefore, I am assuming that instead of '1 Day' being 24 hours, it is anything less than 47 hours and 59 minutes...
Is my assumption correct or do I need to look at something else. Thanks!
Billy
SQL Showing Last 24 Hours
Oct 31, 2005SELECT sysjobs.name, sysjobservers.last_run_outcome, CONVERT(varchar(8), sysjobservers.last_run_date, 112) AS LastRunDate, CONVERT(varchar(8),
sysjobservers.last_run_time, 8) AS LastRunTime, GETDATE() AS CurrentDateTime
FROM sysjobs LEFT OUTER JOIN
sysjobservers ON sysjobs.job_id = sysjobservers.job_id
WHERE (sysjobservers.last_run_outcome = 0) AND (CONVERT(varchar(8), sysjobservers.last_run_date, 112) >= DATEADD(day, - 1, GETDATE()))
I am wanting a similar solution, I need to convert an "int" to a time format, when I use this where ">= DATEADD(minute, - 720, GETDATE())" for the last_run_time I get this error:
Syntax error converting datetime from character string.
8 Hours To Add A Bit Column
Jul 1, 2004I ran the following:
ALTER TABLE Recipients ADD Obscene BIT NOT NULL DEFAULT 0
On a table with 80 million records. It's been running for 8 hours and counting now. This is ridiculous. No one else is using this server.
Server configuration:
SQL Server 2000 Enterprise Edition with SP3a
2 GB RAM
3.0 GHz P4 with hyperthreading
SCSI RAID
Any ideas why this is taking so long? Can I find out what it's doing? Is there anything I can do to make it go faster?
28+ Hours To Shrink?????
May 15, 2008My database is current 135GB. I have been running the following:
DBCC SHRINKFILE([database_data],2)
DBCC SHRINKDATABASE ( [Database],20,TRUNCATEONLY)
It's been running for 28 hours. Should it be taking this long? If I stop the shrink, will it need to rollback?
Thanks!
Run Commands Only Between Hours
Feb 29, 2012I have a job that runs between the hours of 10 PM and 9 AM. It launches a controller stored procedure that will call other stored procedure until the entire process is done.I would like the controlling stored procedure to only call the steps between the hours of 10PM and 9AM also.. So at 8:59 AM it will start the next step, but at 9:00 AM it will exit.
View 6 Replies View RelatedStoring Hours
Mar 17, 2004I use SQL 2K with an Access 2000 project as front end. I have four date fields in a table. The first one should contain a date (eg 20th of February), the second one a starting hour (eg 8:00h), the third one the finishing hour (eg 10:00h) and the fourth should calculate the difference between the second and third column.
So I created four datetime fields. Storing a date in the first column is no problem, but storing only hours doesn't seem to do what I want. When I enter 8:00 from within the Access, it is stored 1/1/1900 8:00 in SQL Server. How can I make sure that the hours are saved with the date of the second column, so either as 8:00 without further information or as eg 20-2-2004 8:00?
Sum Hours Per Cost
Apr 21, 2015I am querying a database that has hours worked listed by pay rate. I want to return sum of hours per pay rates I have been given but hours are stored in multiple columns. So If I run the below query for one worker it returns.
HoursBill_rate_1 Hours_2Bill_rate_2
34.2610.26 0.000.00
5.7410.26 5.689.67
I want to report to the business:
40hrs 10.26
5.68 9.67
The query I have written is below:
Select Sum(Hours_1) As Hours,Bill_rate_1,Sum(Hours_2) as Hours_2,Bill_rate_2--,Hours_3,Bill_rate_3
From Valid_Timesheets
Where (Department = '938' and tax_period = 3 and tax_year = 2015 AND PERSONNEL_REF = '991A001198') and
((Bill_Rate_1 = £10.26 or
Bill_Rate_1 = £9.67 or
Bill_Rate_1 = £8.27 or
[Code] ....
Sum Of Hours By Week
Jul 23, 2005Hi,I have the following query:SELECT p.employee_code, p.employee_name,CONVERT(VARCHAR(12),t.tran_date,101) AS TranDate,CONVERT(VARCHAR(12),t.post_date,101) AS PostDate, SUM(tobill_hrs) ASTotalHoursFROM tat_time t, hbm_persnl pWHERE t.tk_empl_uno = p.empl_uno AND t.tran_date BETWEEN '2005-03-01'AND '2005-03-31' AND p.employee_code IN ('0340')GROUP BY p.employee_code, p.employee_name, t.tran_date, t.post_dateORDER BY p.employee_codeI would like to sum the tobill_hrs by week (Monday-Sunday) instead ofby day. For instance the user will put in a date range (a from dateand to date).Is this possible to do?Thanks!
View 9 Replies View RelatedTime Over 24 Hours
Apr 18, 2006I am creating a report that has call duration and if i run it over amulti-day span the duration is limited to 24 hours and starts over atzero. is there a way to not limit hours?I am usign the following in my select statement:convert(varchar(12), dateadd(s,SUM(ISNULL(Calls.OutgoingCallDuration,0)), '19900101'), 108) AS[Outgoing Call Duration]
View 3 Replies View RelatedGMT Deviation Hours
Dec 14, 2007Is there a function in SQL Server 2005 that can add GMT deviation hours to the existing date. I have a data field with the GMT_Deviation_Hours listed in numeric format e.g. -1, 12 etc. I have another date field to whom i wish to deviate the datetime according to the GMT_Deviation hours.
Thanks
Comparing Hours
Sep 28, 2007Hi, i have this problem :
I have to compare 1 hour between two days , example:
I have this datetime value (BitacoraProcesoUsuario.FechaInicioProceso) in 108 format : 23:35:22
and I make this query:
Select TOP 100 * from BitacoraProcesoUsuario
INNER JOIN Turno
ON convert(varchar,BitacoraProcesoUsuario.FechaInicioProceso,108) > = '24:00:00'
AND convert(varchar,BitacoraProcesoUsuario.FechaInicioProceso,108) <= '06:29:00'
and returns nothing........ this because 23:35:22 is gtreater than 06:29:00' value, my question is how can I compare this hours in this format.
Regards
Working With Hours
Feb 20, 2008Would you help me ?
In the example below, how can I do to have count = 9 ?
It means that I have 9 periods of 30 minuts from 09:00 to 13:30.
Thank you.
declare initial_hour as varchar(10)
declare final_hour as varchar(10)
declare per30min as varchar(10)
declare count as int
set initial_hour = '09:00'
set final_hour = '13:30'
set per30min = '0:30'
set count = (final_hour - initial_hour) / per30min ------>>> count = 9
Subtracting Hours
Feb 18, 2008I would like to know the best method or the simplest method to do the following. Take a datetime value and subtract a given number of hours. I am taking a UTC time and subtracting a given number or hours in a query. I seem to be getting mixed results.
Thanks
What Data Type Is Best For Hours
Feb 1, 2007What is the best data type to use when tracking hours, for example;
2.30 < this is stored in DB
(two hours and thirty minutes)
5.15 < this is stored in DB
(5 hours and fifteen minutes)
SQL/ASP.net Storing Hours Information?
Mar 26, 2008MS SQLJust to confirm, I need to store the number of hours a user has spent doing something, am I correct using the smalldatetime field for the MS SQL database?I will eventually need to be able to calculate totals using SQL Reporting Services so it's important that the hours add up correctly (to 60 minutes, not 100).ASP.NETI'm trying to find the correct sort of formatting to use? Short time stores the time as 9:50AM, which isn't what i'm after.I need to store the hours as in 1 hour, or 01:00:00 (1 hour, 0 minutes, 0 seconds) or just 01:00 for 1 hour. I do need minutes but seconds are not required.I can't find the right formatting for this, would Long time be more suited?
View 18 Replies View RelatedRun A Procedure, Or Query Every 5 Hours???
Sep 7, 2005its possible make a procedure or something inside the sql server to run every 5 hour?? to make a update of a table
View 2 Replies View RelatedCan't Add New Records During Office Hours
Jul 7, 2000I have a SQL 7.0 backend for a new DB of mine. This is the first time I
have developed a SQL backend from scratch, so the problem may be my design.
The problem may lie with my Access frontend (I have tried both 97 and 2k),
but I am inclined to think not considering the behavior.
On only one table (the master table no less), I cannot add records during
business hours. I can add records to other tables and edit records on this
table though. This troublesome table has a field indexed as unique that is
not the PK (don't know if that has any bearing, but it is the only table in
this DB that has that property).
The adding records problem appears to be network traffic related because
sometimes records can be added at lunchtime and definitely after hours. I currently only have 3 connected users. I had these problems with only one connected user, so the DB itself is not being strained by traffic. I often have more traffic on the DB after hours to avoid the problems. I have worked on the connection and network traffic angle for awhile, when I stumbled upon the bizarre part. If I add a record via a stored procedure and run this SP on the SQL query analyzer on the server (no network is involved) it is still slow to add the record (30 minutes or more). Needless to say this would equate to an failure on the front end. The same query will run in 0-3 seconds after hours!
Does anyone have any clue how this could happen? I am stumped.
I do have a budget for a consultant if I need one, but I need to find
someone who has a chance to fix this problem. If you don't know the answer
but can refer someone in the DFW area, I am in your debt.
Debbie
Calculating Business Hours
May 10, 2006I am trying to calculate business hours that an order that is open. The rules are 8am-5pm. For instance the first row, the clock would stop at 17:00, and pick up again at 8am and add on to total business hours. If the order was created after business hours, and the endstamp was before business hours, this would be 0. If created after 5pm friday, and the endstamp was before 8am monday, this would be 0. In the second set of timestamps I have here the order was recieved at 14:39 but took until the following day at 14:49 to be ordered. I imagine I have to use datepart and datediff for this, but other than that I am not sure on how to do it. Any help would really be appreciated!
StartStampEndstamp
6/27/2005 14:356/27/2005 17:41:11
6/27/2005 14:396/28/2005 14:49
6/27/2005 18:486/27/2005 18:54
6/27/2005 11:416/27/2005 11:45
Dates And How To Set Their Hours In Ms Sql Server?
Jul 4, 2004hello this is my store procedure and i want to set the hour of the first date to be 00:00:00 and the hour of the second date to be 23:59:59 but i don't know how to do this,so please HELP me
CREATE PROCEDURE dbo.Proba
@date1 smalldatetime ,
@date2 smalldatetime,
@s float output
AS
Select @s=sum(Pay) from RKO where Dates between @date1 AND @date2
GO
Getting The Number Of Hours In A Month
Jun 23, 2004Hi,
I'd like a function that returns the number of hours in a specific month (or the number of days which I could then multiply by 24). The function would have to consider leap years for February.
Any ideas?
Thanks,
Skip.
Adding A BIT Column - 100+ Hours!
Jul 7, 2004I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.
Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?
Any ideas?
Hours Days And Months
Oct 4, 2007This code displays records by the the month they where recorded and displays the months as names (January...)
How can I do the Same for Days (Mon, Tues) and Hours (9.00,10.00)
Select DATENAME(mm,dDate) as Month, count(iStatID) as Total_Po
from ListingStats
Group by DATENAME(mm,dDate)
Order by DATENAME(mm,dDate)
Delete Files More Than N Hours
Mar 1, 2004Hi everybody,
Can anyone help me out in deleting files more than n- hours from a physical folder using xp_cmdshell ....
Computing Business Hours
Mar 16, 2004I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am. Call Center Hours are 8-5.
Essentially I have a number of different alternatives, and have found some potential solutions, including:
www.dbforums.com/arch/7/2003/9/914261
However, my situation has a couple of additional twists to the standard 8hrs of business M-F. The call center is open different hours depending upon the day of the week. For example, 8-5 M, 10-7 T, 8-5 W Th F, 10-2 Sat, 10-12 Sun
Additionally, I would like to remove Holiday's from the calculation for service level as well.
I have explored a number of different table DTD's, but none seem to be a perfect fit for determining the number of "open" hours between when an interaction arrived, and when it was handled.
The DTD I have for the Holiday table is as follows:
CREATE Table Holidays (HolidayDate DateTime)
GO
Insert Into Holidays (HolidayDate) Values ('12-25-2004')
Please let me know what you feel would be the DTD for storing the business hours and also the query for extracting the number of Open hours between two dates
Thank you in advance
Days Hours Minutes
Apr 18, 2008I have two field and I need to find the sum I guess of them. One is called the clm_dout (process date) and the other one is clm_rcvd (received Date). These are both date and time fields and it looks like this 2006-03-17 00:00:00.000. I need to create a formula that wiil i guess give me the difference and create it into hour and minutes because I an trying to create a turn around report. this is what I had...
TimeValue({clm_doubt}-{clm_rcvd})
This is not working for me. Can someone give me a suggestion please.
Thanks!
Synchronization Running For 2 Hours Already
Jun 17, 2008I went to View Synchronization status and see that the sychronization has been running for two hours already. Is that normal? I thought it would just find the one change I made and update that one change?
Thanks in advance!