Dear all, I have the Following Query:SELECT DATEDIFF(day, GETDATE(), RECENT_RESERVATION)AS Expr1,RECENT_RESERVATION FROM EMP WHERE SUN=empName;when i run it inside the query analyzer, it returns two columns. but if run it inside The ASPX page it retuns only one column wich is RECENT_RESERVATION date.Note: i am using one methode that takes care of reading from SQL and assigning the result into an array, it works fine everywhere, but with this query it dosen't work. Any suggestions??
When I get result from .NET console app, I get correct answer "JungleSektor". However, when SQL Server executes this code, it gives me "NT ServiceMSSQL $ SQL2014". How to get correct result?
with datediff all I can get it to return is months or a total of thedays...so if the difference in dates is 12 months 4 days how do I adjust theSQL to accommodate both?
I just discover the result for my query that had use the function dateDiff seems to be in-correct, no matter what's the date, the dateDiff always return a zero. Am I using it in-correctly? select DateDiff(mm,11/1/2004, 12/31/2005) SELECT component_id, component_description,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 2 THEN component_qty ELSE 0 END) AS mm1,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 1 THEN component_qty ELSE 0 END) AS mm2,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 0 THEN component_qty ELSE 0 END) AS mm3,sum(component_qty) as totalFROM view_jobcomponent WHERE date_complete between '10/1/2004' and '12/31/2004' GROUP BY component_id, component_descriptionorder by component_id, component_description
I've been trying to create a function that returns the difference, in months, between two dates. The DateDiff function (m) returns an integer, but I really need a decimal. I could return the days instead, but it would be difficult to figure out how the number of months from this, especially when the dates span multiple years and I need to adjust for leap year. Does anyone know of a resource that might have a solution for this?
I have a query and am trying to just return the difference between two dates but not include weekends.For instance, if I have 08/21/2006 - 08/28/2006, there are 6 weekdays. I tried this, but I am getting 7 as a result.SELECT DATEDIFF(weekday, request_start_date, request_end_date) AS days_off, request_id FROM request Any help would be greatly appreciated.
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...
Hi,I'm using the datediff function to display the ages of the users in my database. However the age rounds up once they are 35.5 etc...I could create another function which works similar to the DateDiff function, but use math.floor to always round down, but I need to use this function in a SQL statement WHERE clause. Is there any way around this?Thanks,Curt.
I am trying to break up the age into column from a dob field for a cross tab report. I can query the datediff with an alias but can not individually change the columns I need like a virtual or temp column, but can't figure out how to accomplish it.
use 'Database'
Select datediff(Year, dob, getdate()) As "Under 22", datediff(Year, dob, getdate()) As "22-45",
datediff(Year, dob, getdate()) As "46-65", datediff(Year, dob, getdate()) As "Over 65"
from 'Table'
WHERE (DATEDIFF(yy, dob, GETDATE()) < 22 OR
DATEDIFF(yy, dob, GETDATE()) BETWEEN 22 AND 45 OR
DATEDIFF(yy, dob, GETDATE()) BETWEEN 46 AND 65 OR
DATEDIFF(yy, dob, GETDATE()) > 66) AND (status = 'Current' OR
This will give you the time information about how apart two dates are.CREATE FUNCTION dbo.fnTimeApart ( @FromTime DATETIME, @ToTime DATETIME ) RETURNS @Time TABLE ([year] SMALLINT, [month] TINYINT, [day] TINYINT, [hour] TINYINT, [minute] TINYINT, [second] TINYINT, [millisecond] SMALLINT) AS BEGIN DECLARE@Temp DATETIME, @Mts INT, @year SMALLINT, @month TINYINT, @day TINYINT, @hour TINYINT, @minute TINYINT, @second TINYINT, @millisecond SMALLINT
RETURN ENDAnd to test the functionSELECTd.FromDate, d.ToDate, x.* FROM( SELECT'19690906' AS FromDate, '19760608' AS ToDate UNION ALL SELECT'19991231', '20000101' UNION ALL SELECT'20070207', '20070208' UNION ALL SELECT'20000131', '20000228' UNION ALL SELECT'20070202', '20070201' UNION ALL SELECT'20070207', '20070307' UNION ALL SELECT'20000131', '20000301' UNION ALL SELECT'20011231 15:24:13.080', '20020101 17:15:56.343' UNION ALL SELECT'20011231 17:15:56.343', '20020101 15:24:13.080' UNION ALL SELECT'20020101 15:24:13.080', '20011231 17:15:56.343' UNION ALL SELECT'20000131', '20000229' ) AS d CROSS APPLYdbo.fnTimeApart(d.FromDate, d.ToDate) AS x ORDER BYd.FromDate, d.ToDateAnd the output isFromDateToDateyearmonthdayhourminutesecondmillisecond 19690906197606086920000 19991231200001010010000 200001312000022800280000 200001312000022900290000 20000131200003010110000 20011231 15:24:13.08020020101 17:15:56.34300115143263 20011231 17:15:56.34320020101 15:24:13.08000022816736 20020101 15:24:13.08020011231 17:15:56.34300022816736 20070202200702010010000 20070207200702080010000 20070207200703070100000 Peter Larsson Helsingborg, Sweden
I have two date columns one is sent_date and other is approved_date my requirment is to find the difference between the two dates which can be minutes/hrs/days. using datediff function iam able to get it in minusts or hrs but my output should be of the format hh:mm 23:10 (ie 23 hrs and 10 min) or say 48:00 (for 2 days)
recently i created a system using php and mysql to record faults that the ict technicians could get reported faults out of. so far it does everything i want but some senior members of staff now want the front end to give a report of status, one of the things they wish to see is how many jobs have been completed this week month and year
within my tables is a field date. naturally it has the date submited contained within it. the code i have been using to try and get an output is
mysql_query("SELECT date, datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");
the complete feild is another contained within the datebase. i have also tried SELECT * datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");
unfortunatly my limited knowledge of php and sql is holding me back on this. any help guys would be appriacted
I'm trying to use the DateDiff function within my select statement, but I'd like to add the parameter of greater than 30 days. This will have the query only return records where my bill stop date is greater than 30 days from the completion date. I currently have the datediff function within my select statement as
DATEDIFF (d,A.StopBillDate, a.CompletionDate) as [DIFFERENCE]
I would prefer to keep the datediff function within the select statement so as to have difference in days appear as a column within my output.I have been unable to add the parameter of > 30 days to the query without getting an error.
Hi Experts,I am working on SSRS 2005, and I am facing a problem in counting theno of days.My database has many fields but here I am using only two fieldsThey are Placement_Date and Discharge_DateIf child is not descharged then Discharge_Date field is empty.I am writing below query to count the number of days but is is notworking it is showing the error"The conversion of a char data type to a datetime data type resultedin an out-of-range datetime value."select casewhen convert(datetime,Discharge_Date,103) = '' thendatediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())elsedatediff(day,CONVERT(datetime,Placement_Date,103),CONVERT(datetime,Discharge_Date,103))end NoOfDaysfrom Placement_DetailsSo please tell me where I am wrong?Any help will be appriciated.RegardsDinesh
When running a query such as this on a database view:
select count(*) from WWALMDB.dbo.v_AlarmEventHistory2
SQL throws the following error:
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Oddly on a Table the count(*) function works.
Is there a limit on views or the count(*) function that I am not aware of?
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .
I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.
select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC
System.DateTime.Now is a function for getting current date time in C#
The query is neither giving error nor giving desired result.
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed
September: @CurrentDate = 9/10/2007 @EndDate = last working day 9/30/2007
Total# of working days = 15
October: @CurrentDate = 10/1/2007 @EndDate = last working day 10/31/2007
Total# of working days = 23
November: @CurrentDate = 11/1/2007 @EndDate = last working day 11/30/2007
Total# of working days = 22 etc. Any ideas of how i can approch this?
I'm storing records that contain a date/time data type. I am needing two links on a reports page (asp), the first should return all records for the current month and the second link should return all records for the last three months (including current month). I have no idea how to just sort by month.
I'm also not sure what to include here in this post to help you answer my question. On the form that is submitted initially the text field is named "txtSubmitDate" and in the database it's stored in a field called "submitdate" and is 8 characters in length.
I've tried: 'SELECT TODAY'S MONTH SqlJunk = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE())-1"
'SELECT TODAY'S MONTH and the last 2 months SqlJunk2 = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE()) OR MONTH(submitdate) = MONTH(GETDATE())-1 OR MONTH(submitdate) = MONTH(GETDATE())-2 ORDER BY submitdate ASC"
These are not working because it can't handle the change in year (going from january 2005 back to december 2004, etc).
I have a report that is grouped by Month and Year.
So I need it to display:
Jan, 2007 Feb, 2007 Mar, 2007 Apr, 2007 Etc…
AND it needs to be sorted with Jan as the first month and Dec as the last (as if it was 1-12 for months).
Now I can get it to display by taking the month name and concatenating it to the year, but then of course it won’t sort the way I want it to.
Any suggestions on this?
Perhaps I could/should return two values, the first being what I display on the report, and the second being what I sort on as a hidden field? So I could return 200701, 200702, 200703, and so on, to sort on that?
Below is the code. It gives 2 errors. 1 @StartDate must be defined (it is a report parameter) 2 I cannot seem to get this to create a 5 character field (yy/mm) so I can group count results by this field (Along with all the other queried field) Help will be appreciated. DECLARE @t TABLE (Owner nvarchar(5), cdate nvarchar(7), status nvarchar(10), jtype nvarchar(5)) DECLARE @sowners nvarchar(5) DECLARE @mydate datetime DECLARE @sstatus nvarchar(10) DECLARE @sjtype nvarchar(5) DECLARE @chardate nvarchar(7) DECLARE @Jmonth Int DECLARE @Jyear Int DECLARE @Jcharyear nvarchar(4) DECLARE @Jcharmonth Nvarchar(2) DECLARE jobcur CURSOR FAST_FORWARD READ_ONLY FOR SELECT Owners, createdate, UserField2, Jobtype FROM Requirements WHERE (((Owners IS NOT NULL) AND (Owners <> 'Par')) AND (CreateDate >= @StartDate)) OPEN JobCur FETCH NEXT FROM JobCur INTO @sowners, @mydate, @sstatus, @sjtype WHILE @@FETCH_STATUS = 0 BEGIN SET @Jyear = (Year(@mydate)) SET @Jmonth = (Month(@mydate)) SET @Jcharyear = CAST(@Jyear,4) SET @Jcharmonth = CAST(@Jmonth,2) IF Len(Jcharmonth) = 1 BEGIN SET Jcharmonth = '0' + Jcharmonth END IF @Jmonth >= 10 BEGIN SET @chardate = @Jyear + '/' + @Jmonth END IF @Jmonth < 10 BEGIN SET @chardate = @Jyear + '/0' + @Jmonth END INSERT INTO @t (Owner, cdate, status, jtype) VALUES (@sowners,@chardate,@sstatus,@sjtype) FETCH NEXT FROM jobcur INTO @sowners, @mydate, @sstatus, @sjtype END CLOSE jobcur DEALLOCATE jobcur BEGIN SELECT * FROM @t END
Using RS 2000, is there a way to schedule a report for email delivery on the last day of each month. It looks like that would have to be a change made to the Report Manager. Is it possible to reconfigure the report manager to add a last day of the month or month end option?
how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.
Columns are four:
username, current_tenure, move_in_date, and count.
DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)
CREATE TABLE [dbo].[countHistory]( [username] [varchar](50) NULL, [current_tenure] [int] NULL, [move_in_date] [smalldatetime] NULL, [Cnt_Lead_id] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
12/1/07 What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:
If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4). If my date parameter is 7/10/07, the charges would be 18 (7+6+5)
I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:
Code Block
SELECT a.DayCount, a.Sales, SUM(b.Sales) FROM Sales a CROSS JOIN Sales b WHERE (b.DayCount <= a.DayCount) AS RunningTotal GROUP BY a.DayCount,a.Sales ORDER BY a.DayCount,a.Sales Here is the result set i'm looking for:
Charges
Date
1
1/1/07
2
2/1/07
6
3/1/07
9
4/1/07
12
5/1/07
15
6/1/07
18
7/1/07
21
8/1/07
24
9/1/07
27
10/1/07
30
11/1/07
33
12/1/07 Each date's charges are a culmination of 3 months worth of charges.
Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!
