I have asp.net applicatin with SQL database communicating. in database I have date field and time field. Now I wan to do SQL query which can pull informatin on particular date between given start time to given end time
Can some one show me sample SQL query so I can pull informatin on particular day between two times
thank you
maxmax
SELECT
CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate 04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
Hi,
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D
where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
Hello All,
Below carry takes too much time while execution
Select
'PIT_ID' = CASE WHEN Best_BID_DATA.PIT_ID IS NOT NULL THEN Best_BID_DATA.PIT_ID ELSE Best_OFFER_DATA.PIT_ID END,
Best_Bid_Data.Bid_Customer,
Best_Bid_Data.Bid_Size,
Best_Bid_Data.Bid_Price,
Best_Bid_Data.Bid_Order_Id,
Best_Bid_Data.Bid_Order_Version,
Best_Bid_Data.Bid_ProductId,
Best_Bid_Data.Bid_TraderId,
Best_Bid_Data.Bid_BrokerId,
Best_Bid_Data.Bid_Reference,
Best_Bid_Data.Bid_Indicative,
Best_Bid_Data.Bid_Park,
Best_Offer_Data.Offer_Customer,
Best_Offer_Data.Offer_Size,
Best_Offer_Data.Offer_Price,
Best_Offer_Data.Offer_Order_Id,
Best_Offer_Data.Offer_Order_Version,
Best_Offer_Data.Offer_ProductId,
Best_Offer_Data.Offer_TraderId,
Best_Offer_Data.Offer_BrokerId,
Best_Offer_Data.Offer_Reference,
Best_Offer_Data.Offer_Indicative,
Best_Offer_Data.Offer_Park
from
(
Select PITID PIT_ID, CustomerId Bid_Customer, Size Bid_Size, Price Bid_Price, orderid Bid_Order_Id, Version Bid_Order_Version,
ProductId Bid_ProductId, TraderId Bid_TraderId, BrokerId Bid_BrokerId,
Reference Bid_Reference, Indicative Bid_Indicative, Park Bid_Park
From OrderTable C
Where
version = (select max(version) from OrderTable where orderid = c.orderid)
and BuySell = 'B'
and Status <> 'D'
and Park <> 1
and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4)
and Price =
( Select max(Price) From OrderTable cc
where version = (select max(version) from OrderTable where orderid = cc.orderid)
and PitId = c.PitId
and BuySell = 'B'
and Status <> 'D'
and Park <> 1
)
and Orderdate =
( Select min(Orderdate) From OrderTable dd
where version = (select max(version) from OrderTable where orderid = dd.orderid)
and PitId = c.PitId
and BuySell = 'B'
and Status <> 'D'
and Price = c.Price
and Park <> 1
)
and OrderId = (select top 1 OrderId from OrderTable ff
Where version = (select max(version) from OrderTable where orderid = ff.orderid)
and orderid = ff.orderid
and PitId = c.PitId
and BuySell = 'B'
and Status <> 'D'
and Price = c.Price
and Orderdate = c.Orderdate
and Park <> 1
)
) Best_Bid_Data
full outer join
(
Select PITID PIT_ID, CustomerId Offer_Customer, Size Offer_Size, Price Offer_Price, orderid Offer_Order_Id, Version Offer_Order_Version,
ProductId Offer_ProductId, TraderId Offer_TraderId, BrokerId Offer_BrokerId,
Reference Offer_Reference, Indicative Offer_Indicative, Park Offer_Park
From OrderTable C
Where
version = (select max(version) from OrderTable where orderid = c.orderid)
and BuySell = 'S'
and Status <> 'D'
and Park <> 1
and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4)
and Price =
( Select min(Price) From OrderTable cc
where version = (select max(version) from OrderTable where orderid = cc.orderid)
and PitId = c.PitId
and BuySell = 'S'
and Status <> 'D'
and Park <> 1
)
and Orderdate =
( Select min(Orderdate) From OrderTable dd
where version = (select max(version) from OrderTable where orderid = dd.orderid)
and PitId = c.PitId
and BuySell = 'S'
and Status <> 'D'
and Price = c.Price
and Park <> 1
)
and OrderId = (select top 1 OrderId from OrderTable ff
Where version = (select max(version) from OrderTable where orderid = ff.orderid)
and orderid = ff.orderid
and PitId = c.PitId
and BuySell = 'S'
and Status <> 'D'
and Price = c.Price
and Orderdate = c.Orderdate
and Park <> 1
)
) Best_Offer_Data
ON Best_Bid_Data.Pit_Id = Best_Offer_Data.Pit_Id
Can any one please help me?
Thanks
Prashant
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
My asp.net application is attached with SQL database, I record only time in my SQL database , field type is nvarchar. Now I want to do qurey by time and pull the result , but datatype of field in nvarchar, query does not giving me right result.
some one tell me how Do I do query so I can get proper result.
thanks maxmax
How do I find all the records within a time range when all the datetime's are on different days?
Example: Show me all the order placed between 12:00 and 15:00 in May
hi,
how can i define a simple time-out exceed when running a simple select query like
select a from atable where a > 1
and define if query doesn't give results in 5 sec, it should be stopped.
I need to time a query down to hundredths of a second. I can see from the runtime clock in SQL Studio that my query ran for 00:00:00. However, I need to know exactly how long it took.
Thanks for your help!
Hello,I have a table that lists a number of available time slots for a party venue. I want a user to select a particular time slot and have the query show results for that time slot plus two time slots before and two time slots after the user's selection. If the user selects a time slot that is the first of the day, I still want that user to be shown 5 results including the one he selected. The same goes for the last time slot of the day. Can anyone help me with the SQL statement?
Thank you very much in advance!
Mark
I am using SQL Server and ASP.NET. I am executing a couple of stored procedures and displaying the results in a datagrid. Since these Stored procedures takes around 2-4 minutes each, I want to display a status bar on the web by displaying the approximate time the user needs to wait before seeing the results.
My question is: Is there a way to find out the approximate EXECUTION TIME of the stored procedure before hand. Also, if that is possible, how do i access the same from the ASP.NET code..
Thanks
Sathya
Hello,
I have a table which has a field called time and it has data like '23/04/2004 9:43:40 AM'
How would i write a query that would retrieve the last hour of data from now().
i've tried this but does not work,
SELECT [time]
FROM table
WHERE ([time] = { fn NOW() } - 60)
Thanks
Goong
I have a server with two test instances of a data base. I have a query which creates a temp table, inserts 29 rows, perform 4 update queries to add counts and then dumpps out the results. This entire query script runs 1.33 minutes on one instance and 2.5 minutes on the other. On the production server this query now runs in 9 seconds. If I run any one of the test updates individually they execute under 2 seconds, just like the production server.
THe results are repeatable.
All are SQL 7 with all service packs on NT4 sp6. Both test data bases are backups of production from last week. I suspect some kind of caching/buffer problem, but I do not know what to look for. I am not a DBA so I have no idea what role TEMPDB plays may play in this.
Can anyone give us ideas on where to look for the performance difference? Will our impending upgrade to SQL2K solve this problem or make it worse? Any ideas would be appreciated.
Why is the response time to run a query faster when the output is displayed in grid format as compared to text format?
View 1 Replies View RelatedHi, We have a SQL 7 / Win2K cluster and yesterday afternoon the users were complaining about poor performance. Their queries were timing out.. (Not all of them, just some on some large tables)
I ran just an ad-hoc query against the table from my machine and I also timed out. THen I went right to the box that had control of the cluster and did the same thing there and also timed out. Because of time constraints (and we are in testing mode) we tested a failover and everything was back to normal after that.
So now we want to try to figure out what could have been the problem. At the time I checked out the Memory and CPU usage and they were very low (0-5%) and using only 1/3 of the memory. It couldn't be a bad query or index because after the failover it worked fine.
Could there be something wrong with the specific box that had control at the time? I dont' know where to look?
Any ideas would be appreciated!
Hi,
I am running this query and it is taking over 3 minutes.
"select * from table1 where CONVERT(varchar(10),dated,5) = '13-09-01' "
Table1 has a column called dated which is datetime datatype.
Any suggestions how can i optimize this query?I tried Non-clustered index on Dated column and time came down to less than 3 but still more than 2min.
TIA.
Is there a way in sql server to find out how much time a sql server took to execute a query.
Thanx in advance.
Regards,
Samir
This is probably a simple question, but I am relatively new using SQL Queries.
I tried this which gives me half of what I need... SET STATISTICS IO ON
I also need my query to return the run time that it took to run my query.
Can someone help me please?
Thanks!
David
Aside from indexes, will it help if I use multiple filegroups to improve the time needed to query millions of records?
View 2 Replies View RelatedI've been baffled over how to do this without using a script... I would like to get the info I need with a single query.
Here's my scenario, the table looks like this (simplified):
ID, TIME
101, 5am
101, 6am
104, 5am
260, 5am
104, 6am
260, 6am
101, 7am
260, 9am
104, 7am
101, 8am
So basically I have a column of identifications and a column of times. They won't necessarily be in order. I would like a query that gives me this:
101, 8am
104, 7am
260, 9am
It would order the IDs ascending, only showing the newest time assigned to that ID in the table.
Thanks in advance for any help :)
Hey guys and gals,
I'm having a real problem with this query at the moment...
Basically I have to produce a query which will tell me the total number of people employed by the company at any given date and the total salary for all these people.
We have a people table and a career table.
People(unique_identifier, known_as_and_surname, start_date, termination_date ...)
Career(unique_identifier, parent_identifier, career_date, basic_pay ...)
Relationship people.unique_identifier = career.parent_identifier
Employees can be identified like so
SELECT *
FROM people
WHERE start_date <= DateSelected
AND (termination_date > DateSelected
OR termination_date IS NULL)
Passing the selected date to the query is no trouble at all I am just having problems with the point in time side of this.
All and any help is greatly appreciated :)
~George
P.S. SQL Server 2000 ;)
We have a query (a few actually) which runs for about 30 secs via Siebel 6 and the same query takes on 1 or 2 secs in Query Analyser, consistently. Naturally this performance problem is causing issues. We're wondering if the App isn't using the same execution plan, or using the indexes or....
It might be worth noting that the query returns 1 or no rows.
(I can add the query and more detail if anyone really, really wants :rolleyes: )
The devlopers have created a VB app which mimmics to app running the query and we've put it through proflier, results..
Duration Reads CPU
Siebel Query 28300 3661280 23984
It seems a high number of reads there, and the result from QA is SO much faster.
Any ideas welcome, thanks.
i want to query all my records and how many per date
so
select count(id),date from registrants group by date
but my problem is the date is a date time field so how can i just group it by date but not count the time?
My table like as follow
Results
Date | Time | Value
---------------------------
4/4/2007 | 0 | 879
4/4/2007 | 5 | 600
4/4/2007 | 10 | 390
4/4/2007 | 15 | 490
4/4/2007 | 20 | 290
...
...
...
4/4/2007 | 100 | 290
4/4/2007 | 105 | 290
4/4/2007 | 110 | 290
...
...
4/4/2007 | 1210 | 290
4/4/2007 | 1215 | 290
Date is DateTime, Time is Integer and Value is Integer
If Time=0, mean 1200 AM
If Time=5, mean 1205 AM
If Time=1210, mean 1210 PM
Let's say Current Date=4 April 2007 and Current Time=1206 AM
I want to display data which is Date>=4 April 2007 and Time>=1206 AM
How to query to get expected result shown as follow
Date | Time | Value
---------------------------
4/4/2007 | 10 | 390
4/4/2007 | 15 | 490
4/4/2007 | 20 | 290
...
...
...
4/4/2007 | 100 | 290
4/4/2007 | 105 | 290
4/4/2007 | 110 | 290
...
...
4/4/2007 | 1210 | 290
4/4/2007 | 1215 | 290
I have a query to determine the time range like following
SET @StartDate = CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))as datetime)
SET @EndDate = GetDate()
What time range that query set ??
i have following sample query
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))
select Datetime
from InterfaceTraffic
[code]...
those query would get me a return of value inside of datetime coloumn, which is, date of (assume i run the query today) 2014-05-12 07:00:00 until 2014-05-30 16:00:00, my question is where is the date of 2014-05-01 07:00:00 until 2014-05-11 16:00:00 goes ??
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))
select Datetime
from InterfaceTraffic
where (DateTime between @StartDate and @EndDate)
[code]...
it would get me a return of all dates of last month with all hours and what i want is to have a return of all dates of last month but with specific hour only (the hour between 7 am until 5 pm)
A query that runs in a second or so in Query Analyzer requires 20 seconds in a linked Access Project.
What's the secret of MS_Access poor performance, and can it be improved?
Tom Stuart
I have a Stored Procedure that execute some queries on link server. It takes so long to complete so my application get timeout error. There was no problem until last week. I suspect, remote queries that qorks on link server takes long. How can i trace the time of queries. Any idea about link server timeout problems?
Thanks in advance.
Dear All,
here i'm posting my query which is taking 3 minutes
please suggest me the best query
SELECT distinct INP.COLUMN001 REPORT_INPUT_ID, INP.COLUMN002 REPORT_ID, INP.COLUMN003 OPERATION_ID,
OPER.COLUMN004 OPERATION_CODE, OPER.COLUMN005 OPERATION_NAME, INP.COLUMN004 ITEM_ID,
CONVERT(NVARCHAR , INP.COLUMN005, 110) RECEIVED_DATE, INP.COLUMN006 LOT_NO, INP.COLUMN007 RECEIVED_QTY,
INP.COLUMN008 CONSUMED_QTY, (select CODE from view1 where item_id = INP.COLUMN004) my_val,
(select NAME from view1 where item_id = INP.COLUMN004) Item_Name, INP.COLUMN009 UOM_ID,
U.UOM_CODE, INP.COLUMN010 BASE_RECEIVED_QTY, INP.COLUMN011 BASE_CONSUMED_QTY,
case when INP.COLUMN012 ='1' then 'Progress' when INP.COLUMN012 ='2' then 'Closed' end OPERATION_STATUS,
case when INGDTL.COLUMN006 ='0' then 'Ingredient' when INGDTL.COLUMN006 ='1' then 'Intermediate' end INPUT_TYPE,
INP.COLUMNB01 COLUMNB01, INP.COLUMNB02 COLUMNB02, INP.COLUMNB03 COLUMNB03, INP.COLUMNB04 COLUMNB04,
INP.COLUMNB05 COLUMNB05, INP.COLUMNB06 COLUMNB06, INP.COLUMNB07 COLUMNB07, INP.COLUMNB08 COLUMNB08,
INP.COLUMNB09 COLUMNB09, INP.COLUMNB10 COLUMNB10, INP.COLUMND01 BRANCHID, INP.COLUMND02 COMPANYID, INP.COLUMND03 CREATEDBY,
INP.COLUMND04 CREATEDDATE, INP.COLUMND05 LASTUPDATEDBY, INP.COLUMND06 LASTUPDATEDDATE, INP.COLUMND07 ROWGUID,
INP.COLUMND08 UPDATEDSITE, INP.COLUMND09 LANGID, WC.COLUMN009 WIP_WAREHOUSE_ID,
(SELECT (sum(WIP.COLUMN011) - sum(wip.column010))
FROM TABLE066 WIP where wip.column008 = INP.column004 and WIP.COLUMN005 = '8cd741c7-1ac6-4839-88e7-df85518170f1' and wip.column006 = inp.column003 ) WIP_Qty ,
WIPM.Column005 WIP_ITEM_ID
FROM TABLE073 INP
left join view1 I on I.ITEM_ID = INP.COLUMN004
left join view2 U on U.UOM_ID = INP.COLUMN009
left join TABLE022 OPER ON OPER.COLUMN001 = INP.COLUMN003
left join TABLE066 WIP on WIP.column008 = INP.column004
left join TABLE015 WC on WC.COLUMN001 = OPER.COLUMN008
left JOIN TABLE040 INGDTL ON INGDTL.COLUMN002 = INP.COLUMN004 AND WIP.column008 = INGDTL.COLUMN002
left join TABLE065 WIPM on WIPM.column005 = INP.column004
where INP.COLUMN002 = '057f87aa-7884-43fa-8984-9b74c971da62' order by my_val
thank you very much
Hi, when I execute the sql in DBTestArchive and then DBTest query analyzer , I found that the run time is shorter in DBTest. Can I make assumption that the query run time is shorter in DBTest if I select data from it and insert into another database? Thanks.
INSERT INTO DBTestArchive.dbo.tblVendorMasterArchive
SELECT * FROM DBTest.dbo.tblVendorMaster
Consider this SQL Query:-----------------------------------------------------------------SELECT c.CASE_NBR, DATEDIFF(d, c.CREATE_DT, GETDATE()) AS Age,c.AFFD_RCVD, c.PRV_CRD_ISS, x.RegE, x.Type, x.Fraud,c.CUST_FN + ' ' + c.CUST_LN AS CustFullName,c.ATM_CKCD_NBR, x.TotalLoss, x.Queue, x.Status,c.QUEUE AS Expr1, x.CHECK_ACT_NBR, c.CUST_LN, c.SSN,c.CREATE_DTFROM (SELECT TOP 9999999 cl.CASE_NBR, cl.SSN, cl.CREATE_DT,SUM(cast(TRANS_AMNT AS float)) AS TotalLoss,glQueue.REFN_NM AS Queue,glStatus.REFN_NM AS Status,grRegE.REFN_NM AS RegE, grType.REFN_NM AS Type,grFraud.REFN_NM AS Fraud, cl.CHECK_ACT_NBRFROM (((((T_CASE_LST AS cl LEFT JOINT_INCIDENT_LST AS il ON cl.CASE_NBR = il.CASE_NBR)INNER JOIN T_GNRL_REFN AS glQueueON cl.QUEUE = glQueue.REFN_NBR)INNER JOIN T_GNRL_REFN AS glStatusON cl.STATUS_CD = glStatus.REFN_NBR)INNER JOIN T_GNRL_REFN AS grRegEON cl.REGE_CD = grRegE.REFN_NBR)INNER JOIN T_GNRL_REFN AS grTypeON cl.CASE_TYPE_CD = grType.REFN_NBR)INNER JOIN T_GNRL_REFN AS grFraud ON cl.FRAUD_CD =grFraud.REFN_NBRWHERE (((glQueue.REFN_DESC) = 'Queue')AND ((glStatus.REFN_DESC) = 'STATUS_CD')AND ((grRegE.REFN_DESC) = 'YesNo')AND ((grType.REFN_DESC) = 'Fraud_Code')AND ((cl.STATUS_CD) = 0))GROUP BY cl.CASE_NBR, glQueue.REFN_NM, glStatus.REFN_NM,grRegE.REFN_NM, grType.REFN_NM, grFraud.REFN_NM,grFraud.REFN_DESC, cl.CHECK_ACT_NBR,cl.SSN, cl.CREATE_DTHAVING (((grFraud.REFN_DESC) = 'YesNo'))) xLEFT OUTER JOIN T_CASE_LST c ON x.CASE_NBR = c.CASE_NBR-----------------------------------------------------------------1. Is there anything that can be done to speed up the query?2. This part of the query: ... AND ((cl.STATUS_CD) = 0 ... where the 0is actually a variable passed in via a VB application. 0 would be newcases, and normally return around 4000 - 5000 records.3. The SQL server, Web Server, and users, are all in different states.4. The time to return this query where cl.STATUS_CD = 0 is about 7 -12 seconds.5. Is this a reasonable time for this query? What can be done toincrease the time?6. The SQL server is indexed on T_CASE_LST.STATUS_CD andT_INCIDENT_LST.CASE_NBR, but not on any field from T_GNRL_REFN sinceT_GNRL_REFN is only a general lookup table, and contains less than 50records.7. I've built the query as a stored procedure, and it works, though nomeasurable speed increase was obtained.8. I have not attempted building a view to aid this, as I don't seethat helping... or will it?9. Well: any ideas?10. I would gladly rewrite the SQL Query if it could return the samedata faster using another method.11. Is there a way to accomplish the joins involved with theT_GNRL_REFN in another manner to make it quicker?12. Is there a better way to add the values in T_INCIDENT_LST than:.... SUM(cast(TRANS_AMNT AS float)) AS TotalLoss ... ?13. I don't care if its pretty, I just need it faster.14. How can I get the summing of T_INCIDENT_LST.TRANS_AMNT without aderived table...? I know that using the derived table is slowing itdown some.**** Any Ideas ****David
View 3 Replies View RelatedHi All.I have some rather large SQL Server 2000 databases (around 60GB).I have set up jobs to re-index the tables and update statistics everysunday. This worked will for a few months. Now after a day or two ofusing it the connections to it keep timing out. If i start the jobsmanually, all is well for two days or so.Surely there can be a better solution to this ?TIA.Ryan,.
View 2 Replies View Relatedi am using sqlserver 2000, and i was wondering how do i go aboutsetting the query time out. is there a way to configure the querytimeout for a specific user id?
View 1 Replies View Related