I'm using the following SQL String in an ASP script with SQl Server:
SELECT time, DATEDIFF(minute, time, '18:00:00') AS difference FROM appointments
I have two appointments in the database - one with a time of 11:00 and one with a time of 14:00.
When I run this SQL query, 'difference' comes out exactly 48 hours more than it should do. So for the first appointment, it says that the difference is 3300 minutes and for the second it is 3120 minutes.
Any idea why this is happening? Is it something to do with the fact that I'm not using dates in the datetime format?
Hi All, I have a DB of 100Gigs. I have a SP which I came to knw frm developer that it is taking some more than 20 mins. Though when I just run the code frm the SP and replace the variables in the query with the values which I use in the executing the SP it completes in 2 mins.
I have recomiled the SP, drop and recreated the same. I have updated the statistics of entire database checked whether the biggest table is having any defragmentation.
Any clues on this weird behaviour. I have SQL Server 2000 with SP3.
id (int) campaign_id (int) run_date (smalldatetime) user_id (int) result (text) result_id (int)
result_id is an int 1-4 and I am inner joining another table that holds the Campaign_Title on campaign_id. What I would like to be able to do is select the Campaign_Title and then a sum of each of the result_id's but I need to do this by run_date. I would then like to be able to bind it to a datagrid. I have been racking my brain for days now and I can't seem to make the query return the correct results.
Please help! Let me know if you need more information!
There is an int filed in my table called "WeekNo" and when I use order by WeekNo Desc, I am getting the following result. 9 8 7 7 6 5 4 3 2 18 17 16 15 15 14 13 13 12 11 10 10 1
This does not seem right, can anyone comment why i am getting this result.
I am working on an application with a number of tables (which I can't change) that have data of the form a1, b1, c1... a2, b2, c2...a3, b3, c3... The tables contain 5 to 10 sets of data in each row.
I want to create a stored procedure to return a cursor with a row for each set of data for a row of the table. I know I can use unions:
select a1 as a, b1 as b , c1 as c... from sometable where pk=@pk
union
select a2, b2, c2... from sometable where pk=@pk
union
select a3...
Is there a way that is more efficient (eliminates requerying the table and all the unions)?
hi there! i am a newbie with replication. i am trying to implement one, however due to lack of references, there are some difficulties. here are some of my questions, i am hoping anybody could take their time to help me out in details. Thanks and appreciate it!
(1) i could not publish all tables. tables that do not have primary keys are not included, and i need to publish all tables.
(2) tables that i sucessfully published does not replicate the table's primary keys, relationships and triggers, how do i update the subsciber?
(3) for some reason, i could not replicate schema objects - views, UDFs, stored procedures. its giving me an error.
I have created a very simple report that includes a single table with three cells per row.
When I view the report in Visual Studio, on the Report Server, in any format, it looks fine. However, the version that gets emailed as a subscription looks awful. The width of my table cells is not preserved, so they shrink to be as narrow as possible with all the words wrapping.
Basically, the table gets smooshed so that it's much narrower than it should be. All the words wrap when they shouldn't.
CanShrink is set to False. I have tried CanGrow on both False and True and it didn't make any difference.
Remember, the problem is only with the emailed version of the report.
I have a ForEach Loop that has 3 script tasks in it.
I have them set up so that they execute in order, such as:
script1 ---> script2 ---> script3
script1 creates a file
script2 creates a file
script3 compares the files using a diff command
Problem is, when I execute the container, it shows that script3 finishes BEFORE script2, which of course gives an error b/c the file from script2 doesn't exist yet.
The error is "The system cannot find the file specified".
I am working for my client on SQL 2005 Enterprise & observed a strange behaviour. Consider a table(EMPTABLE) as below:
PK, INT
VARCHAR
EMPID
EMPNAME
1
A
2
B
3
C
4
D
5
E
6
F
7
G
8
H
9
I
10
J
Now, I wanted the 5th highest EMPID from 2 from the table. Now, we know that the SQL
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2 should (from all other possibilities) return me the desired value as the last row of the output table. I tried to get this in a derived table & get the MAX value i.e.
SELECT MAX(EMPID) FROM ( SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2 ) der
But this returns me 10, when I would've thought 7 was the expected answer
I remodified my query as mentioned below to strangely get 7 as the result... SELECT MAX(EMPID) FROM ( SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2 ORDER BY EMPID ASC ) der
How did the ORDER BY hep it???
Also, if I use SELECT MAX(EMPID) FROM ( SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >2 ) der
I get the same result without having to specify the ORDER BY in my derived TABLE...
I tried looking for similar threads. But nothing could explain this to me...
If anyone knows what I am not able to understand, please let me know.
I have an update trigger on a table. When a specific column is updated, I get the rowid from 'inserted' and then pass it via service broker to another database that will fire off a maintenance routine at a later time. This whole process seems to work fine if I update a single row at a time through Query Analyzer.
During testing (of the service broker part) I found that if in Query Analyzer I run an update that updates all of the records at once, then the trigger seems to fire only once for the entire process, therefore killing the rest of my process.
I would have thought that regardless of how a record was being updated the trigger would fire atomically for each row.
I have got a set of queries to run on a table stored in my SQL Database and this query creates a new table which I export to excel for final results. NOw I need to design an SSIS package for this query to run and to export the result in excel sheet. I started with the control flow by adding the TSQL statements to it and then in the data flow I connected it with my database. But when I execute it, it always gives me an error. I have just started learning SSIS but have not been able to figure it our properly.
I've seen this error on several of my databases again and again this week:
Violation of PRIMARY KEY constraint 'PK_XXXX'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (whatever number here). The statement has been terminated.
The thing is, the PK is unique, and the INSERT statement does not touch that column, it touches the other ones. So SQL2012 is the one that automatically generates the next and unique available value.
How can be possible that the value to be inserted (generated by SQL2012) is a duplicate one? By the way, this was not happening on the previous SQL2005 or SQL2008 server where the databases were being hosted.
Here's my SQL 2012 version: 11.0.3000.0 (SP1 applied). Runs on top of a Win2008R2 Cluster.
I am unable to send a message to a target service on a different SQL Server instance. Using the SQL Profiler, I get the following error from the target machine:
Broker::Connection: Connection handshake failed: An OS call failed: (80090326) 0x80090326(The message received was unexpected or badly formatted.). State 106.
On the initiator side, the transmission_status shows the following error:
An error occurred while receiving data: '64(The specified network name is no longer available.)'.
Any ideas on why the target thinks the message is "unexpected or badly formatted"? Btw, I am specifying the default message type and contract for the services.
This is a sql question, so I'm hoping someone can give me some direction. I need to query a table and return all rows where the individual is >= 18 years of age. It should involve simply subtracting the date of birth from the current date. I thought the answer would be so simple: select * from table where datediff(yy,dob,getdate()) >= 18 Unfortunately, the results are inconsistent. The DATEDIFF function, it appears, will not show correct results down to the month/day level. I tried posting this on a sql server forum, but the recommendations I received involved coding some gigantic function. It seems that there ought to be a simple 'WHERE' clause which could solve this. If someone knows of a silver bullet, I would surely appreciate your advice. Thank you.
How can i calculate datediff (in minutes) between two consecutive rows without using CTE & Temp tables?
I was using this successfully, but i need something without CTE and temp tables (both of them are not supported in the tool i am trying to generate a report using custom query).
WITH CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY Id desc) AS RowNo, * FROM MyTable) SELECT t1.*, ISNULL(DATEDIFF(mi, t2.CreateDate, t1.CreateDate), 0) AS Duration FROM CTE t1 LEFT JOIN CTE t2 ON t1.RowNo = t2.RowNo - 1 ORDER BY t1.Id desc
I am going put in the whole query but my question is on changing a datediff minutes amount into DD:HH:MM.
Query is: select c.APPLICANT_ID as [Applicant ID], aetc.EVENT_TYPE as [Event Type], cast(aetr.CREATE_DATE as date) as [Registration Date], cast(aetc.CREATE_DATE as date) as [C Creation Date], datediff(mi,cast(aetr.CREATE_DATE as datetime),cast(aetc.CREATE_DATE as datetime)) as [time diff],
[Code] .....
I want this as dd:hh:mm rather than just minutes.
Currently this field is just minutes so a figure such as 20, depending on the time difference between the 2 dates.
I have tried the convert statement convert (char(5) ...........,108) within this but that's not working and I have used floor before to do this type of thing but not sure where that should go.
So I am trying to work out the difference between today's date (GETDATE()) and a Target Date in a specific table (targetdate)
When I use the DATEDIFF function it is including non working days in the calculation (weekends and bank holidays). Although our date calandar table provided to us from a third party supplier will tell you the weekends, it does not tell you the bank holidays.
Luckily there is another table in the database called - ih_non_work_days.
The format of the date is "2014-12-25 00:00:00.000" for example in that table.
How do I using my "targetdate" and today's date calculate in days the differance - excluding the dates that exist in the ih_non_work_days database?
So for now my basic script looks like -
SELECT com.comm_reference AS 'Referance' ,com.current_task_target_date AS 'TargetDate' , DATEDIFF(D,com.current_task_target_date,GETDATE()) AS 'Incorrect Date Calculation' FROM [dbo].[em_communication] as com
i've tried using the SELECT SQL sentence on PPC with UNION and INNER JOIN in combination with DATEDIFF function which works fine on ordinary SQL but obviously does not work on compact edition. Is there some workaround?
SQL sentence that works:
Code Snippet
SELECT * FROM ( SELECT 'O' Type, O.Name , OC.Name Contact, Birthday FROM OutletContact OC INNER JOIN Outlet O ON OC.OutletID=O.OutletID UNION SELECT 'W' Type, W.Name,WC.Name Contact, Birthday FROM WholesalerContact WC INNER JOIN Wholesaler W ON W.WholesalerID=WC.WholesalerID ) S WHERE Birthday IS NOT NULL AND (DATEDIFF(day,GETDATE(),Birthday) BETWEEN 0 AND 14)
If I wanted to get everyone DOB who is over 18 how would I do that? I am currently trying something like this, but no luck...
Declare @todays_date datetime
Select from person CASE WHEN dateadd(year, datediff (year, Date_Of_Birth, @Todays_Date), Date_Of_Birth) > @Todays_Date -- Date of Birth check THEN datediff (year, Date_Of_Birth, @Todays_Date) - 1 ELSE datediff (year, Date_Of_Birth, @Todays_Date) END >= 18
I'm using a datediff(mi, start, stop) to get the duration of an operation. i want it displayed in HH:MM format. can anyone help me w/ a way to do that????
SELECT weight FROM progress WHERE dateInput = (SELECT MAX(dateInput) FROM progress) AND memberID = 1
The problem is that the MAX dateInput doesn't belongs to memberID 1. It belongs to memberID 2. What I want is that I wanna choose the MAX Date of memberID 1. I thought of maybe using datadiff function. But I don't know how to make the datediff statement. Maybe I can write the datediff statement whereby the least datediff between the dateInput and getdate() will be the row I want. I really appreciate the person that helps me this problem... Thanks 1st of all!
I am trying to select records from whatever the current date would be and 12 months before whatever the current date is. How would I go about doing this. The table that I am trying to do this with has a year column and a month column.
I was playing with the date diff function, but I can only get dates from the specified date range. I need it to be where if I run it tomorrow, it will get that day and everything within the last 12 months.
Hi there i am using the datediff funtion but it does not seem to be inclusive of the two dates eg SELECT DATEDIFF(day, s_Date, e_date) AS NumberOfDays,* FROM weekendtest
i know i could do SELECT DATEDIFF(day, s_Date, e_date)+1 AS NumberOfDays,* FROM weekendtest
but i was just wondering if there some other function i should use that would be more appropriate
Hi I've a problem!!! i want to use datediff thats no problem. But the first parameter has to be parameterised. What datatype do i declare it as. i declare it as varchar it returns me an error : Invalid parameter 1 specified for datediff.
i'm doing this in a stored procedure.
Is there a way out or do i've to use the good old 'IF' or 'Case' ?
Hi, I have a Table Where I have Two Date columns and I want to find the Differnce in the 3rd column.ie DateReceived - AsOnDate = NoofDays.I tried this code SELECT SRNO,CONVERT (VARCHAR,AsonDate,102)as ASONDATE,CONVERT(VARCHAR, DateReceived, 102) AS DateReceived,CONVERT(VARCHAR, DateAcknowledged, 102) AS NoofDays, sum(CASE WHEN DATEDIFF(DAY, DATERECEIVED,ASONDATE, GETDATE()) FROM Details
Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00. My code : datediff(Minute,'01:00:00','23:00:00') The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours).... Can anybody help ??? Thanks in advance...
I'm trying to use the same select statement I used in classic SQL, but it isn't working. I get a "The server tag is not well formed." error. I'm trying to return all the records where the date in a particular field are over 60 days old. My select statement is: "SELECT [ReNewAd], [Male or Female], [File2], [PHOTO], [First Name], [Last Name], [City], [State], [NewAd] FROM Members Where ('NewAd' IS NOT NULL AND (DateDiff('d', [NewAd], '" & Now() & "') <= 60)) order by [NewAd] Desc" I've been looking through the datediff posts, but I'm not making any sense of what I'm reading. Can anyone see what I'm doing wrong? Thanks, Diane
I'm trying to create a stored procedure that will return a set of data. An input parameter (if specified) will determine what topics will be show based on the date the item "datetime". ALTER PROCEDURE [dbo].[discussions_GetTopics]@board_id as int,@PageNumber INT,@TopicsDays INT = NULL,@TopicsPerPage INT,@HowManyProducts INT OUTPUTAS-- Declare the table that will store all the topics for the given board_idDECLARE @Topics TABLE(RowNumber INT,topic_id INT,topic_title VARCHAR(50),topic_replies INT,topic_views INT,topic_type INT,post_id int,post_time DATETIME,Topic_Author_UserName nvarchar(256),Topic_Author_ID uniqueidentifier,Post_Author_Username nvarchar(256),Post_Author_ID uniqueidentifier)-- populate the tableINSERT INTO @TopicsSELECT ROW_NUMBER() OVER (ORDER BY discussions_topics.topic_id), discussions_Topics.topic_id, discussions_Topics.topic_title, discussions_Topics.topic_replies, discussions_Topics.topic_views, discussions_Topics.topic_type, discussions_Posts.post_id, discussions_Posts.post_time, user_1.UserName AS Topic_Author_Username, user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_IDFROM discussions_Topics INNER JOIN discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_idWHERE (discussions_Topics.board_id = @board_id ANDCASE @TopicsDays WHEN '1' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 1 WHEN '2' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 7 WHEN '3' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 14 WHEN '4' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 30 WHEN '5' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 90 WHEN '6' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 180 WHEN '7' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 365END)-- return the total number of products using an OUTPUT variableSELECT @HowManyProducts = COUNT(topic_id) FROM @Topics-- Return the specified page of topicsSELECT * from @TopicsWHERE RowNumber > (@PageNumber - 1) * @TopicsPerPageAND RowNumber <= @PageNumber * @TopicsPerPage I need help where the "CASE @TopicsDays WHEN '1' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 1 WHEN '2' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 7 WHEN '3' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 14 WHEN '4' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 30 WHEN '5' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 90 WHEN '6' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 180 WHEN '7' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 365 END" Is... Im not doing it right. Can someone help me?
hello, i have a Pictures table: PictureID, Name, Description, DateAdded (GETDATE() when insert), IsActive... i need to make some stored procedures to show me the pictures added in last 24hours, in last 3 days, last 2 weeks and so on the pictures added in database are active (available to be seen by users) only 1yaer after the date added I tryied to make a stored procedure (in fact i maked a lots of them, for 1day 3 days 1 week 1 month), but i have a problem with that DateDiff and DateAdd Here is what i tryied CREATE PROCEDURE LastAdded_2monthsAgo
AS
SELECT Pictures.ProductID, Pictures.Name, Pictures.Description, Pictures.DateAdded FROM Pictures WHERE (DATEDIFF(month, Pictures.DateAdded, GETDATE()) >= 0) AND (DATEDIFF(month, Pictures.DateAdded, GETDATE()) <= 2) ORDER BY DateAdded DescI have a feeling that is wrong, please make your own version and show me what i should write...I don't know what should be first the today date or the DateAdded...i need to select the last added products from a specific interval of time...Should i do something with that "1 year available" like WHERE (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) >= 0) AND (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) <= 2) I am sure is a stupid thig up there...if you can, make your own version how you would do it and show me..please help me
