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?
Thanks,
Rob
Hi, I've just noticed that there is a proc in my database using a person's age in a where clause but it seems to be rounding the age. The idea is select records where the age (using the person's birthdate) is between @xMonths and @yMonths.
Code Snippet
select datediff(month, '4-30-2006', '4-1-2008');
Result: 24
If 4/30/06 is the birthdate and 4/1/08 is today, it needs to be 23 months. Can someone explain how it determines the month boundaries and how to exclude partial months?
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
Hi GUYS
I am trying to use the datediff function to return a Y if difference is greater than 0 and a N if difference is less than or equal to 0
select if datediff(dd,maturity_date,log_changed) > 0 'Y','N')
and i am getting a syntax error.Can someone show me where i am getting it wrong.
In god we trust,everything else we test.
Hi,
I have the following SSAS MDX query, currently taking a From & To date. How would I alter the MDX below so that just the prior 3 months data was returned for a chart of balances. I do not wish to retain the date parameters
SELECT NON EMPTY { [Measures].[EOD Book Balance] } ON COLUMNS, NON EMPTY { ([Time].[Simple Date].[Simple Date].ALLMEMBERS * [Products].[Product ID].[Product ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@FromTimeDate, CONSTRAINED) : STRTOMEMBER(@ToTimeDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Type].&[Term] } ) ON COLUMNS FROM ( SELECT ( { [Book Balance Type].[Balance Type].&[Credit Balance] } ) ON COLUMNS FROM [DailyBalances]))) WHERE ( [Book Balance Type].[Balance Type].&[Credit Balance], [Products].[Product Type].&[Term] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
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.
I currently have a stored procedure that returns a list of dates based on a date range a user enters.
CREATE PROCEDURE sp_GetContactScheduleDates
@MonthFrom int,
@YearFrom int,
@MonthTo int,
@YearTo int,
@DaysInMonth int
AS
Select distinct s.ScheduleMonth, s.ScheduleYear
From OnCall_Schedules s
Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
>= CAST(cast(@MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@YearFrom as nvarchar) as smalldatetime)
And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
<= CAST(cast(@MonthTo as nvarchar) + '/' + cast(@DaysInMonth as nvarchar) + '/' + cast(@YearTo as nvarchar) as smalldatetime)
Order by s.ScheduleYear, s.ScheduleMonth
GO
However, this only brings back those dates that are in the table. I need to get ALL dates within the range.
For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?
Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.
Can some sort of function be used? What would the code look like?
Hi
I regularly use the T-SQL date functions to return the 1st of a particualr month.
e.g.
SELECT DATEADD(m,DATEDIFF(m,0,getdate()),0)
returns 2007-05-01 00:00:00.000
i.e the first of the current month at midnight.
However, when I try to use a similar expression as a derived column in SSIS it returns a completely different date.
DATEADD("month",DATEDIFF("month",(DT_DATE)0,GETDATE()),(DT_DATE)0)
returns 30/05/2007 00:00:00
Any ideas why and how I can obtain the first of a particualr month using SSIS derived column?
I want to line up across the top of a cross tab Jan-12 Feb-12 ... July-13 Aug-13 up to and including the latest month in the db. What's the best way to achieve this?
I currently create crosstabs with case statements as follows:
select
sum(case month(date) when 1 then value) as Jan,
sum(case month(date) when 2 then value) as Feb
from db
I figured you could create some sort of while loop for the case statements but I can't get the syntax right.
Or another strategy would be to use the Pivot method and build [Jan], [Feb], [Mar] etc through a loop?
I have the following query:SELECT Month, Sum(Hits) AS Hits FROM tblHits GROUP BY Month ORDER BYMonthUnfortunately it only returns rows for months that have data assignedto them.How can I tweak this so that months 1-12 are returned, and Hits = 0 formonths with no data in the base table?Thanks.
View 2 Replies View RelatedDear 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??
View 6 Replies View RelatedI have a data set that returns the following
Customer
pncount
pn%
monthval
ABC
5
30
01/01/2007
DEF
9
20
01/01/2007
GHI
4
40
01/01/2007
ABC
DEF
4
90
02/01/2007
GHI
7
10
02/01/2007
ABC
4
5
03/01/2007
DEF
GHI
6
100
03/01/2007
but I need this
01/01/2007
01/01/2007
02/01/2007
02/01/2007
03/01/2007
03/01/2007
abc
5
30
4
5
def
9
20
4
90
ghi
4
40
7
10
6
100
Any Idea Help
I have a requirement to use DateDiff(Months,DateTime1,DateTime2) and this must return positive integer values.
Currently negative numbers are being returned because DateTime1 < DateTime2  or DateTime1 > DateTime2 .
The DateTime1 and  DateTime2  values are dynamic and either of them can be bigger than the other.
Any query solution so that always positive value is returned.
Hi !
I'm doing something like :
SELECT AVG(timeConsumed) as average_time_consumed
FROM table1 where ...
The value return is 42, however if i do this using a calculator the result is 42,39583
How can i get the decimal part ?
I have tried this : AVG(timeConsumed)*1.0, but the value returned is 42.0
Hello,
Below statement in tsql returns int value (0).
SELECT 5/10
How to force it to return decimal value (0.5)?
I tried following but it did not help me
1) SELECT Convert(numeric(7,2),5/10)
2) Declare @Var1 numeric(7,2)
SET @Var1 = SELECT Convert(numeric(7,2),5/10)
SELECT @Var1
Thanks.
Hi. I have a query that returns several averages and percentages. Is there some way to set up the query so the results only go to 2 decimal places? Here is a sample of the query I am using:
Code:
$query = "SELECT COUNT(deal_id), SUM(vs), SUM(vs)/COUNT(deal_id)*100, AVG(fin) FROM sales GROUP BY salesperson";
It works great,except the results are several decimal places long, and I need it to be only 2.
Any help appreciated.
Cori
Never mind, I found it muhself....
FORMAT(AVG(fin),2) works perfectly!!
I am trying to have an Excecute SQL Task return a single row result set executed on SQL Server 2005.
The query in the Execute SQL Task is:
select 735.234, 2454.123
I get a conversion error when trying to assign to SSIS variables of type Double.
I have nothing configured in the "Parameter Mapping" tab.
I have the two SSIS Double variables mapped to the Tesult Name 0 and 1 in the "Result Set" tab
I don't want to use a for loop enumerator since there is a single row returned.
I simply want to assign these two values to SSIS Double variables (double is the closest match)
I can't even hack this by converting the decimals as string and then using DirectCast to convert them to Double.
Thanks for the help
I have the following table
FeedBack Type Date
test2 positive 03/15/08
tes3 negative 03/01/08
.. ....
in my page i need to select the number of negative/positive comments within the last
1 month, 6 months, 12 months
How can I accomplish that?
thanks
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
View 2 Replies View RelatedHello.
My database stores the decimals in Spanish format; "," (comma) as decimal separator.
I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.
Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:
Error converting data type varchar to numeric
Any knows how to resolve.
Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.
Hello!
I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?
Thanks for giving me any advice,
Ziga
I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl
I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?
View 6 Replies View RelatedHi all,
I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.
e.g.
‚¬1,500,123.00 to ‚¬1.500.123,00
Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.
Any help would be much appreciated
Thanks!
I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.
I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.
It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).
Is the decimal limited to scale 4 ???
Thank you for your help!
Anna
Hi,
I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.
File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.
Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 Å“&
20060715 0001010100010101
Please help!
Thanks
Mirudhu
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
CREATE procedure [dbo].[Marketing_extract_history_load_TEST]
@table_name varchar(200),
@i int output
as
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
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????
tia,
e3witt
Hello,
My string is:
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.
I need to get a listing of all persons who are atleast 18 years of age. A date of birth field in the database is in this format:
4/25/2008 12:00:00 AM
My solution would be where the difference between the current date and the dob is >= 18.
I tried...
select * from table where datediff(yy,dob,getdate)) >= 18.
But this only seems to subtract the years and ignore the days/months, which I need. Could anyone provide the syntax I need?
Help is appreciated. Thanks.