Anyone know of an easy way to work with datetime columns ignoring the time part.
I want to "group by" the date so it is vital that the time part goes.
But I still want to work with the resulting date as a date (i.e. use date functions) so I would prefer to keep the datetime data type.
Jeff writes "I am trying to write a query to retrieve information that was processed between two times of the day (2:00 pm to 6:00 pm) from a date range... I am perplexed; I am new to SQL and am trying not to become frustrated, but this seems to be beyond my skill level. Any guidence would be greatly appreciated!"
View 8 Replies View RelatedHi there, I'm a little bit stumped on this one.
I have a column in a table that records when the date and time of an event took place.
Table Name: Chronicle
Column Name: Created (of type DateTime)
I would like to select the Chronicle records that are between two dates.
(e.g. 1 May 2001 and 20 May 2001)
And I would like to select those records that are between two times.
(e.g. 6:00am and 1:00pm)
Does anyone know how to do this or have any pointers for me?
I can see it would be easier if I had the date in one column and the time in the other.
Can it be done without doing that?
cheers,
Hamish
Hamish Norton
hamish@liftrider.com
I have a tricky question to Microsoft SQL Server 2000/2005. I have failed to find a solution to the problem yet and others have failed too. So I thought that I throw it around here, because the fact that a lot of knowledgeable DBAs hang around in this forum.
I am looking for a script/stored procedure that is able to show me upcoming job executions for a selected date/time range based on the current settings for the jobs configured on the Database server.
Example
Date/Time From: 2/16/2007 00:00 a.m. (Friday)
Date/Time To: 2/17/2007 12:00 p.m. (Saturday)
A. Job 1 / Schedule 1 = Week Days, 1:00 a.m.
B. Job 1 / Schedule 2 = Saturdays, 10:00 a.m.
C. Job 2 / Schedule 1 = hourly, between 10:00 a.m. and 1:00 p.m.
D. Job 3 / Schedule 1 = every 3rd Saturday of the month, 5:00 a.m.
E. Job 4 / Schedule 1 = every 1st Friday of the month, 8:00 a.m.
The routine I have in mind would not return Job E, because 2/16/2007 is the 3rd Friday of the month and not the first.
Job A: 2/16/2007 01:00 a.m.
Job B: 2/16/2007 10:00 a.m.
Job B: 2/16/2007 11:00 a.m.
Job B: 2/16/2007 12:00 p.m.
Job B: 2/16/2007 01:00 p.m.
Job D: 2/17/2007 05:00 a.m.
Job A: 2/17/2007 10:00 a.m.
Job B: 2/17/2007 10:00 a.m.
Job B: 2/17/2007 11:00 a.m.
Job B: 2/17/2007 12:00 p.m.
Output
It needs to return the Job ID, the Job Name, the Schedule ID, the Date and the Time.
Disabled Jobs and Schedules are by default excluded from the selection, but an option to include or exclude those would be a bonus.
Information such as the min, max and average execution time would be great too.
Notes
The schedules of Job D and Job B overlap as you can see in my example above.
This happens only once per month though. I have over 20 jobs with sometimes very frequent execution times, like every 5 minutes or every 20 minutes and jobs that run hourly, daily, weekdays only, weekends only, monthly once etc.
Purpose
I want to do two things.
I want to determine where jobs overlap, not just by start date/time, but also by average run time and maximum run time.
I also want to be able to generate a report that shows me what should have been done and what was actually done by the jobs (note on the site, 6 of the jobs create new jobs on the fly for other database servers and this is sometimes not happening properly, without getting any error message. The volume of jobs makes the manual search like a search for a needle in a haystack.)
Findings so far:
I did some digging myself and found following stored procedures that do some of the steps that I need and involved tables for the calculation.
Stored procedures:
- sp_get_schedule_description (in db: msdb) (undocumented stored procedure)
- sp_add_schedule (in db: msdb) http://msdn2.microsoft.com/en-us/library/ms187320.aspx
Tables:
- msdb.sysjobs
- msdb.sysjobschedules
- msdb.sysjobhistory
It is not a problem to determine the next execution of a job, but that is not what I need, anyway.
The problem is that it does not help you to determine all upcoming execution times, if the selected timeframe is long enough that SQL Server executes the job more than once.
There is no way around using the sysjobschedules table and calculate the execution dates and times based on the configured settings. See the Stored procedure: sp_get_schedule_description.
That one breaks down nicely the settings as documented for sp_add_schedule at
http://msdn2.microsoft.com/en-us/library/ms187320.aspx, but it does not allow the determination of the exact upcoming dates and times when the job is supposed to be executed.
Another Example
If there is only one job scheduled to run
1) every 5 minutes,
2) on every weekday
3) between 1:30pm and 2:00pm
You would get the following results
1) start date/time 7/7/2007 12:00pm, end date/time 7/8/2007 2:00pm
nothing, because the 7/7/2007 and 7/8/2007 are on the weekend
2) start date/time 7/5/2007 12:00pm, end date/time 7/5/2007 1:45pm
7/5/2007 1:30pm
7/5/2007 1:35pm
7/5/2007 1:40pm
7/5/2007 1:45pm
3) start date/time 7/5/2007 1:45pm, end date/time 7/6/2007 3:00pm
7/5/2007 1:45pm
7/5/2007 1:50pm
7/5/2007 1:55pm
7/5/2007 2:00pm
7/6/2007 1:30pm
7/6/2007 1:35pm
7/6/2007 1:40pm
7/6/2007 1:45pm
7/6/2007 1:50pm
7/6/2007 1:55pm
7/6/2007 2:00pm
Now SQL has a lot more configuration options for the scheduler.
And don't forget that you can have more than one schedule record for any single job, including no-schedule record (which would not interest me).
Autom. when SQL starts freq_type=64
Starts when CPU idle freq_type=128
One Time On Date mm/dd/yyyy at time: hh:mm:ss am/pmfreq_type=1
or
Recurring
Occurs
Daily freq_type=4
Every x day(s)freq_interval=x
Weekly freq_type=8
Every x week(s) onfreq_recurrence_factor=x
Mo [ ], Tu [ ], We [ ], Th [ ],
Fr [ ], Sa [ ], Su [ ],
1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, 64 = Saturday. Examples: Su and Mo enabled = 3 (1 (Su) + 2 (Mo)), Mo, We and Fr enabled = 42 (2 (Mo) + 8 (We) + 32 (Fr))
Monthly freq_type=16
Day X of every Y month(s)freq_interval=X
freq_recurrence_factor=Y
or
The 1st,2nd,3rd,4th,LAST WEEKDAYfreq_type=32
of every Y month(s)
freq_relative_interval=1,2,4 (3rd),8 (4th),16(last)
freq_interval= 1=Su,2=Mo,3=Tu,4=We,5=Th,6=Fr,7=Sa,8=Day,9=Weekday,10=Weekend day
freq_recurrence_factor=Y
Occurs Once at hh:mm:ss AM/PMfreq_subday_type=0x1
or
occurs Every X Hours/Minutes
Starting: hh:mm:ss A/PM freq_subday_type=0x4 (minutes) or 0x8 (hours)
Ending: hh:mm:ss A/PMfreq_subday_interval=X
active_start_time
active_end_time
Start Date mm/dd/yyy End Date mm/dd/yyyyactive_start_date
oractive_end_date
No End Dateactive_end_date=99991231
Does anybody has a script that does that or several individual scripts that would have to be combined to do what I want to do?
Thanks. I appreciate it.
Cheers!
Carsten Cumbrowski
http://www.sqlhunt.com/
SQL 2005 Dev
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[code]....
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
View 1 Replies View RelatedHi,
I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
Any advice will be greatly appreciated!
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
I have set up an sql server which has a column called times. This column is to hold race times however when i enter times such as 22:20:89 with the 89 being milliseconds i am not bale to input this it will only allow me to put times in where milliseconds are below 60. This is a problem especially for the shorter races where half a second is a big difference.
Thanks Mike
When trying to connect to a server on a slow connection (satelite), Enterprise Manager times out. I am able to connect with Query Analyzer. Tried to change 'remote login timeout', but that did'nt help, EM seems to timeout long before this value is reached. Any ideas ?
Brgds,
Odd Oren
SQL Statement.
Adding times together.
I am trying to add a list of times together retrieved from my database.
They are from seperate records in the same table.
They are in a date format e.g. 01-Jan-2006 03:45:00, what I want to do is ignore the 01-Jan-2006 part as well as the :00 AM part and focus on the hours and minutes. I wish to add these up e.g. 03:45 + 04:45 = 08:30. Is there an easy way of doing this?
Below is the SQL statement of getting times from my database if its any help.
SQL StatementSELECT LENGTH_OF_VISIT FROM NOV.MAIN;
Returned Results
30-Apr-2007 02:00:00 AM
30-Apr-2007 01:00:00 AM
01-Jan-2006 02:30:00 PM
01-Jan-2006 03:10:00 PM
30-Apr-2007 01:00:00 AM
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
Here is the error that I'm getting:
Code Snippet
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
SELECT
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT Trans1.TR_Amount
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT SUM(Trans1.TR_Amount)
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev
FROM dbo.EntityDef INNER JOIN
dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN
dbo.CurrentDate INNER JOIN
dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate
WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')
ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate
I appreciate all your help
Thanks
Dear All,
I need to select records between two datetimes.
My Database Structure and sample values are as below
Fromdate ToDate
2007-02-20 09:00:00.000 2007-02-20 12:00:00.0002007-02-20 08:00:00.000 2007-02-20 12:00:00.0002007-02-20 06:00:00.000 2007-02-20 13:00:00.000
Query i used
select * from tablename where ((fromdate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103)) or (todate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103)))
My query is not returning values when i am querying between '2007-19-2 10:00' and '2007-19-2 11:00'
Please help
Thanks
Hello everyone,
I have an odd issue and I can't seem to figure it out. I am using SQL Server 2005 and I am using Transactions in combination with Try/Catch blocks. My query (executing the stored procedure) never seems to finish the query in query analyzer. I have removed all statements that exectute or select, etc upon the database except for a simple Select 'test' as test so I would have something in my Begin/End blocks. Below is my stored proc:
ALTER PROCEDURE [dbo].[ar_mainCheckRecon]
@tripID int,
@payment money,
@adjustment money,
@serviceType tinyint,
@checkTpId int,
@checkTlId int,
@checkNbr varchar(20)
AS
BEGINSET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN select 'test' as test
END TRY
BEGIN CATCH
RAISERROR('Error commiting transaction', 16,1)
ROLLBACK TRAN
SELECT '0' as success
END CATCH
COMMIT TRANSELECT '0' as errorCode
END
I call upon the sproc by running this line in query analyzer:
exec ar_mainCheckRecon 433636, 0, 500, 0, 500, 0, 23
Is there something I am overlooking as to why the query keeps running and never stops? My current execution time is at 3 mins accoring to query analyzer!
Thanks,John
When using a SQL Server DateTime column to store just times, e.g. '6:00 PM' I've noticed some weird inconsistency.
If I insert a time in a SQL statement, such as... INSERT INTO myTable (StartTime) VALUES ('6:00 PM')
It winds up going in as '1/1/1900 6:00 PM'. This is okay. This is in fact what I want (unless of course the base day for sql server is in fact 12/30/1899, which is my next question).
If, on the other hand, I insert a time value by hand, using SQL Server Management Studio, then after I insert '6:00 PM' it winds up as '<Today's Date> 6:00 PM', i.e. '1/20/2008 6:00 PM'.
Also, if I insert it using a SQL Parameter, such as...
da.InsertCommand.Parameters.Add("@StartTime", SqlDbType.DateTime, 8, "StartTime")
...Then, if StartTime is 6:00 PM again, it also winds up going in as '<Today's Date> 6:00 PM'.
Is there a way to control this behavior (other than to programmatically append a base date to the time I'm inserting)?
Finally, what is the base day for SQL Server 2005? Is it 1/1/1900 or 12/30/1899? I think I've seen both.
Thanks!
Aaron
Hi
I'm new in SQL. I have a big problem
Database: MSSQL 2000
I have two tables: (results) one with numeric results and one with names (importance_scale)
SELECT * FROM results
name i1 i2 i3 i4 i5
---- ---- ---- ---- ----
john 2 1 2 2 2
anna 0 2 2 2 2
phil 2 2 2 2 2
dave 1 0 2 2 2
SELECT * FROM importance_scale
imp_value imp_name
--------- ------------
2 very important
1 important
0 not important
i1,i2 etc. value it is a imp_value from table 'importance_scale'
I would like to make report which will show all results in this format (in example only first record):
name i1 i2 i3 i4 i5 etc.
----- --- --- --- --- ---
john very important important very important very important very important etc.
I was thinking about using CASE I don't want use cursor. What is the best way?
Thanks for your help
I want to enter times for running event performances (like 10:26.35 for a person's 2-mile time, for instance). I am using sql server 2000. This is an ongoing concern for me since I do a lot of work with running events. Can someone give me some direction as to how I can best deal with this?
The datetime data types do a lot of converting to 'time-of-day' and that is not what I want. For instance, if I enter a 2-mile time of 10:26.35 (a pretty typical 2-mile time) my db converts it to 10:26:35 AM.
If I use varchar data type there is a ton of code to write when I want to sort the data.
Thanks!
i have a column containing employee name with datatype varchar.
i want to find out that how many times does letter 'y' appear in employee's name.
am a beginner to sql
help appreciated
cheers
I'd like to take two DateTimes, subtract them and insert the result as HH:MM:SS,
I think you use datediff but I'm not clear on the datediff formatting to get back HH:MM:SS...
Thanks for your help!
--PhB
Hi I'm new to SQl..I have some monthly data which is being encoded as Varchar...Say forthe month of January 2005 it wud look like '200501'. For each of mymonth I have a coloums which gives me datetime and volume of the job.This is what I'm doingSelectMonth,Time ,Sum(Volume)[color=blue]>From Table A[/color]Where (( Month like '200501%' ) and (Time between '2005-01-0102:00:00.000' and '2005-01-31 06:00:00.000))group by month, timeorder by monthPRoblem: What I want is all the volume between 2am and 6am on every dayof the month?The above query naturaly will give me everything between first and lastof the month...Any help is appreciated...Balaji
View 2 Replies View RelatedI am using an MS Access front end to input data into a SQL serverdatabase. From Access I am generating an update query utilising aninline function. My problem is that when I update a time value,instead of updating the field with something like 08:00:00 it updatesit to 1/1/1900 08:00:00. Is there a way to force the update to onlyuse the hours minutes and seconds and ignore the date part?Any help would be greatly appreciated.RegardsTrevor
View 1 Replies View RelatedHi guys,I have a field in my DB called EventDate as a DateTime field,therefore it holds both the date and time together like this:'2004-10-14 08:42:57.000'.I need to add together all the times in this column for a particulardate range (BETWEEN).Any suggestions will be great.ThanksSunny:)
View 10 Replies View RelatedHi,
I am developing an app that manages race results and I need some advice on the best way to store the race times in sql server. As far as I know, the best datatype would be datetime.
With the data stored, I will need to be able to do things such as:
rank the results based on the race time
Hello,
I have been working on this stored procedure, it pulls the results that i want, but it takes a very long time to execute and about half the time it times out, i have tried changing the timeout settings on the sql server to 0 (no timeout) but it seems to have made no difference.
Could anyone help with making the query a bit more effiecient? SELECT DISTINCT
webStats.id, webStats.ip, webStats.useragent, browsers.browsername, os.osname, webStats.datestamp, webStats.hourstamp, webStats.hostname,
webStats.refurl, webStats.refhost, webStats.entrypage, webStats.visitcount, webStats.country, searchengines.enginename,
IpToCountry.COUNTRY AS CountryName
FROM webStats LEFT OUTER JOIN
IpToCountry ON webStats.country = IpToCountry.CTRY LEFT OUTER JOIN
searchengines ON webStats.useragent LIKE '%' + searchengines.agentstring + '%' LEFT OUTER JOIN
browsers AS browsers ON webStats.useragent LIKE '%' + browsers.agentstring + '%' LEFT OUTER JOIN
os ON webStats.useragent LIKE '%' + os.agentstring + '%'
WHERE (webStats.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE())
ORDER BY webStats.datestamp DESCThanks for your help.
Bart
I have some code that opens a SqlConnection to a MSSQL 2000 database. The connection opens great everywhere except inside my implementation of IPasswordProvider.GetPassword(). It just hangs there, and then throws a timeout. I have tried using both trusted connections, and userid/password connection strings. I notice that within GetPassword, the authenticated user is COMPUTERNAMEASPNET, whereas in the web method that actually is getting called, the authenticated user is my personal user account. The SqlConnection opens fine in the actual web method.
Can anyone tell me what could stop a SqlConnection from opening while inside GetPassword()? I kinda need to look up passwords in the database.
Hi All...
if i had the following sp...
*******************************************************
create procedure my_insert (param1 int, param2 int, paramx int)
as
...
complicated insert routine
...
return
*******************************************************
and then i wanted to exec this sp in another procedure i would have
exec my_insert( 1_value, 2_value, 3_value )
My question is how could i exec this will the result set of a select.... something like this
exec my_insert (select 1_value, 2_value, 3_value from another_table).
I know i could have this in an insert result type statement ie...
insert into dest_table (select 1_value, 2_value, 3_value from another_table)
but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement
Many Thanks
Gary T
I scheduled a Bulk copy operation, and have been getting some inconsistent failures.
Last night the job was set to run every 3 hours, I expect it to take about 1 hour to finish.
The second itteration of the job failed, the first and third ran fine.
Here is the error from DTS
Any suggestions? Is there a better/stable Data Provider for SQL?
... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSBulkInsertTask_1 DTSRun OnError: DTSStep_DTSBulkInsertTask_1, Error = -2147217900 (80040E14) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 3621 (E25) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7399 (1CE7) Error string: OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040... Process Exit Code 1. The step failed.
I have an VB.NET web app which performs a fairly complicated SQL query. It seems in the morning, the 1st time the page is loaded (and query executed) it takes up to 10-15 seconds to complete loading. Sometimes it even times out. However anytime after that, the page loads up (even from another computer) in about 4-5 seconds. Can someone explain the reason for this and how I might fix the load times in the morning?
Thanks
The developers of frond end ask to select only one row from one table, but many times, such as 10. That means the output has 10 rows and same.
Any suggestion will be appreciated.
ZYT
Hello,
I see the following problem regarding a SQL function that returns a value.
We have the following query
SELECT c.Id_Customer, c.Name
FROM t_Customers c
WHERE c.Id_Status = fn_GetParameter('ID_Status.Active')
The idea is not hardcoding the status and other values on each query, and, since SQL Server does not support the definition of constants, we have a table with many parameters and we search them.
We defined the function "WITH SCHEMABINDING" and the SQL Server recognizes it as DETERMINISTIC, so I do not understand why it executes the function as many times as records in the t_Customers table, since every time it is executed it returns the same value.
We could define a variable, assign the value returned by this function to this variable and then use it on the SELECT, but this approach is useless if we use SQL instead of stored procedures (for example, in reports from reporting / BI tools).
Any explanation about why SQL chooses to execute the function many times, and any hint regarding how to make SQL Server execute only once the function will be very appreciated.
Thanks in advance,
Lisandro.
Hey, everyone. I'm new to database programming in SQL Server, and I currently have the 2005 express edition. I was wondering whether it was possible to check whether one time (h:mm AMPM) is less or greater than another (the other in this case being the GETDATE). I already made a user-defined function in order to extract the time from a datetime, since SQL Server doesn't support the time data type. The thing is that the value is returned as a char(8) in the 12-hour style HH:MM AMPM format and I'm sure that's what's screwing up the comparison function. What would I need to do to compare two times (24-hour or 12-hour, whichever is easier)?
View 7 Replies View Related