How To Get Date Difference In Terms Of Year,month,days

Mar 31, 2008



I am using oracle 10G DB as back end.I have two date fields in a table.


1)premium_paying_start_date

2)premium_paying_end_date


i have to get the premium_term i.e, the difference between the two dates(premium_paying_end_date-premium_paying_start_date).

The difference should show the year,month and no of days difference.

For example :

premium_paying_start_date : 14-10-1984

premium_paying_end_date : 01-03-2008

Difference should be : 23 Y : 4 M : 15 D (Y = years, M = months , D= days)


So please give me the solution for this.

View 6 Replies


ADVERTISEMENT

Transact SQL :: Display All Days Of A Given Month And Year

Oct 17, 2015

I need a simple query to display all the days of a given month and year

View 2 Replies View Related

To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year

Aug 30, 2004

I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:

CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()

And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?

I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?

Thank you,
Cesar

View 4 Replies View Related

Find Difference In Month And Year In Sql Server 2005

Jun 17, 2008

--find day,month,year
--for day select datediff(d,'01 may 2008',getdate())
 -- --for month select datediff(m,'01 jun 2006',getdate())
-- --for year select datediff(year,'01 jun 2006',getdate())
above working fine but suppose difference is 1 year 4 month and 2 month 15 days then It's giving 1 year and 2 month respectively.
but I want completely so I can use this in case of expired user in my project.
User can be expired in 1 month,3 month and 1 year. So I'm not able to recognize.
 thanks

View 3 Replies View Related

Getting Date In Day/month/year Instead Of Month/day/year

Feb 2, 2004

I am trying to get my db to return a date in the format day/month/year but its returning the american version month/day/year.

I'm using a DatePart function that converts my date:


CREATE FUNCTION dbo.DatePart
( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
RETURN ( CONVERT(varchar(10),@fDate,101) )
END


This returns te american version, can anyone help me to get this to convert the UK way.

Thanks

View 3 Replies View Related

Extract Date,month, Year From The Date Getting From Sql Table

Apr 14, 2008

Hello All,
i have three textboxes in a page and i want fill those textboxes  with the date, month,year respectively.....
i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..?
Any help..
Thanks..
Anne

View 3 Replies View Related

How To Calculate A Date Difference In Days

Apr 2, 2007

Suppose I have these two days fields
ddold 1/1/2005 12:00:00 AM
ddnew 2/1/2007 12:00:00 AM

How can i get the DateDifference of these two dates in days.

View 4 Replies View Related

Return Number Of Days By Year From Date Fields

Dec 3, 2013

I need to associate aggregate gross_revenue with calendar year, but do not have a date field that reflects payment dates, just contract periods a start_date and an end_date. The contract periods are typically 1 or 2 years and can start at any time I.e start_date 6/1/2012, end date 5/31/13. I think by finding the number of days that fall in each calendar year and storing in a temp table, I can create a simple formula to associate revenue to each year.

View 2 Replies View Related

Date Query Problem Last 8 Days, Different Year From Current

Feb 19, 2008

Hi,

I'm having problems creating a query that brings the results based on last 8 days on date column (column9) and diferrent year from the current one.

If I run the each filter,




Code SnippetYear(Column9) <> Year(GetDate())




and




Code Snippet
(Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 8), 0)) AND (Column9 < DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))



it returns the right values.

So far I've got (it returns no results):




Code Snippet
SELECT Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10, Column11, Column12, Column13, Column14,
Column15, Column16, Column17, Column18, Column19, Column20, Column21, Column22, Column23, Column24, Column25, Column26, Column27,
Column28, Column29, Column30, Column31, Column32, Column33, Column34, Column35, Column36, Column37, Column38, Column39, Column40,
Column41, Column42, Column43, Column44, Column45, Column46, Column47, Column48, Column49, data_anulacao, data_instalacao
FROM Contratos01
WHERE Year(Column9) <> Year(GetDate()) AND (Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 8), 0)) AND (Column9 < DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))
ORDER BY Column1


Please give me your input.

THX.

View 10 Replies View Related

See If Date Is Same Year And Month And Day

Mar 8, 2008

Hi,

I need to see if a record has been inserted into a table, and the condition is if the record has been inserted for the current year and month and day, if not, I can insert a new row.

SO I need to do:

IF NOT EXISTS (SELECT * FROM myTable WHERE Created = @Created)

But it has to be comparing the year,month and day (excluding the time part of the datetime type).

View 9 Replies View Related

Transact SQL :: Calculating Date Difference In Days

Apr 16, 2015

I'm trying to calculate the time difference between a date field and today's date in days. The date field is not mandatory and can therefore be blank. I'm trying to execute the following query:

SELECT employee_code, Civil_ID, DATEDIFF(Day, Civil_ID, GETDATE())
FROM ODEV_VIEW_Credentials_Expiry_Dates
WHERE Civil_ID IS NOT NULL AND Civil_ID != ''
ORDER BY employee_code

I keep getting the following message:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Warning: Null value is eliminated by an aggregate or other SET operation.

No matter what filter I use to process non-blank dates, it never works.

View 12 Replies View Related

Want To Concatenate Year And Month To Get A Date

May 14, 2004

Hi,

My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)

I'll appreciate your help.

YearMonthDerived Date
----------------------
2004101-Jan-2004
2004201-Feb-2004
2004301-Mar-2004
and so on ....


Many thanks in advance. I'll appreciate your help
P.S.
Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them

View 5 Replies View Related

Want To Concatenate Year And Month To Get A Date

May 14, 2004

Hi,

My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)

I'll appreciate your help.

YearMonthDerived Date
----------------------
2004101-Jan-2004
2004201-Feb-2004
2004301-Mar-2004
and so on ....


Many thanks in advance. I'll appreciate your help
P.S.
Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them

View 7 Replies View Related

Last Year Month To Date Function

Feb 16, 2006

I have been spoiled by some report writing tools that have intrinsicfunctions like Last Year Month-to-date. I'm looking for a way to emulatethis in SQL Server now with my fields that are date/time.I'm thinking I need to develop a user defined function to accept a dateinput parameter, but I don't know where to start.Help/Examples appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies View Related

Transact SQL :: How To Get Month And Year For A Given Date

Jun 3, 2015

I have a query for which in the where clause i use where Year(openDate) = Year(GETDATE()) and Month(OpenDate) = Month(GETDATE())-1 which would give me the data i needed for this year last month. However if i run this query on Jan 2015 or Jan 2016, this query would fail.

how to modify my where clause so that it runs regardless of even if its Jan ?

View 6 Replies View Related

Only Month And Year From Date Time

Apr 21, 2008

Hi All,

How do I show only month and year from datetime data type field?

As a example; If the date is 01-10-2008.
I wanna show the date in my report as Jan - 08

Thanks

View 17 Replies View Related

Transact SQL :: Number Of Days Between A Date And Its Month End

Sep 23, 2015

I have a column which stores a set of dates. I want to tell how many days left of a date till it’s month end. It should be noted that month ends are taken from the date series, not a calendar month end.
 
Something like below,
 
DateTD       Days left
2009-01-05  14
2009-01-06      13
2009-01-07      12
2009-01-08      11
2009-01-09      10
2009-01-12       9
2009-01-13       8
2009-01-14       7
2009-01-15       6
2009-01-16       5
2009-01-19       4
2009-01-20       3
2009-01-21       2
2009-01-22       1
2009-01-23       0
2009-02-02       /
2009-02-03       /

View 28 Replies View Related

Transact SQL :: Date Difference In The Form Of Days And Hours?

May 21, 2015

declare @siva1 datetime;
declare @siva2 datetime;
set @siva1='2014-03-10 05:02:11'
set @siva2='2014-03-12 23:52:11'

i want output like this 2.18 means 2day 18hours difference how using query

View 6 Replies View Related

How To Get Month And Year From A Date Which Is Of Type Varchar(20)

Dec 2, 2011

In my table I am using varchar to save a date like this 2-dec-2011. Now I have a requirement where i need to select employee who where present in a month so for that I need a query so that i can pull out all the employees who are present in dec like :

Code:
select * from emp_attendence where date = 'dec-2011'

But this returns nothing ...

View 8 Replies View Related

Extract Month & Year From Date Column

Feb 6, 2008

hi

i have column in database as account open date

format as:Jan 27,2004 12:00:00:AM

How do i extract month& Year from this column..
all i have to do a calculation

if accountopendate is prior to dec 31 1994 then jan 1995..

and if the account open date is after 2100 then ist jan 2011.

how do i write the calculation

Thanks guys
phani

View 11 Replies View Related

Simple Way To Determine The Number Of Days In A Month For Any Given Date

Jul 20, 2005

DECLARE @varDate datetimeDECLARE @varMonthDate datetimeDECLARE @varYear datetimeDECLARE @varFOM varchar(10)DECLARE @NumDaysInMonth as intselect @varDate = '2/1/2004'select @varMonthDate = MONTH(@varDate)select @varYear = YEAR(@varDate)select @varFOM = CAST(CAST(@varMonthDate as int) as varchar) + '/1/' +CAST(CAST(@varYear as int) as varchar)select @NumDaysInMonth = DATEDIFF (day,@varFOM,DATEADD(mm,1,@varFOM))select @NumDaysInMonth as DaysInMonth

View 1 Replies View Related

Transact SQL :: How To Get Date Difference Between 2 Dates In DAYS Excluding Weekends

Oct 14, 2015

Here I have 2 Dates. CreatedDttm & ModifiedDttm.

I want  - DATEDIFF(Day,CreatedDttm,ModifiedDttm)  and I have to exclude the Weekend days from that query result.

View 10 Replies View Related

Display First Date If Split Into A Year And Month Column?

Mar 27, 2012

How can I find the first date if the date is split into a year and month column?

I thought of using the Min function, which would work for the year column, but it would probably just return a 1 everytime in the month column.

View 2 Replies View Related

SQL Server 2012 :: Just Get Month And Date Only In A Query Without Year?

Mar 18, 2015

This is what I have for a query but i would like to take the year off. is there a way i can just have the month and day without the year attached?

View 3 Replies View Related

Creating Birth Date From Day, Month, Year Fields

Nov 14, 2007

I have 3 fields (DOB_YEAR, DOB_MO, DOB_DAY)

They are populated like this (1985, 12, 21)

I would like to take the 3 values of populate one field (BIRTH_DATE) with these combined values to look like this (19851221) BIRTH_DATE is an 8 character field in datetime format.

The code

UPDATE DOB
SET BIRTH_DATE = (dob_year+dob_mo+dob_day)

Returns 6/11/1905 for 1959,11,17

View 5 Replies View Related

Transact SQL :: How To Concatenate Year / Month And Day Into Actual Date

Jan 10, 2012

I am working with a SQL Server database that was set up to store the year, month, and day in separate columns, rather than use a single column for the date. I plan to change this so that we store dates and times using the datetime data type. Until then, for now, I need to write transact-SQL select statements to concatenate the year, month and day to create a date that can be displayed in the results window in the format yyyy/mm/dd.

I can't seem to find any built-in function in SQL Server that will let me do this. Of course, using Excel or Access, I can use the DATE() function to reconstruct the date into yyyy/mm/dd. Is there a way to reconstruct the date into yyyy/mm/dd in SQL Server? 

View 7 Replies View Related

SQL Server 2012 :: Converting Year And Month Varchar To Date

Jan 31, 2014

I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.

Year Month Productname
2013 11 ACB
2013 11 CDE

I would now like to add another column called date and store these Year Month as a date to my existing table

Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01

Is there a way I can do it for all the columns of the existing table ??

View 3 Replies View Related

T-SQL (SS2K8) :: Finding Previous Even Numbered Month And Appropriate Year From Given Date

Mar 25, 2014

I'm trying to write some T-SQL to return the previous even numbered month and appropriate year from given date.

Examples given:
03-25-2014 should return 02-xx-2014
01-01-2014 should return 12-xx-2013

View 2 Replies View Related

To Display Month Name And Year Instaed Of Complete Date...in SSRS Reports

May 6, 2008

Hi All,
I have a sample data like this..I have added the cases for the particular worker in the table..My question is that when displaying the cases for the particular month...the date should not be displayed..instead of date 2008-04-30 I have to display...like April 2008...if the date is 2008-10-20...it should display as October 2008...can anyone please help me with this...
312 KRISTI WHITE 865400 2008-04-30 2
312 KRISTI WHITE 1000264311 2008-04-30 3
312 KRISTI WHITE 1000430815 2008-04-30 1
312 KRISTI WHITE 1000660614 2008-04-30 1
312 KRISTI WHITE 1002371318 2008-04-30 2
312 KRISTI WHITE 2003722520 2008-04-30 4


Thanks
Chaitanya.

View 10 Replies View Related

Transact SQL :: Function To Find Last Date Of Month One Year Ago - RETURNS ERROR

Apr 28, 2015

I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example,  it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:

Operand type clash: date is incompatible with int

Why is this error being returned?

Here is my function:

CREATE FUNCTION dbo.zEOM_LY_D(@Input Date)
       RETURNS date
AS
BEGIN;
  DECLARE @Result date;
  SET @Result =  convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101)
    RETURN @Result;
END;

View 11 Replies View Related

Grouping My Month/year Desending By Month/year

Dec 11, 2006

i have some classes that I want to group by month/year (note:i dont need the day of the month)
how do i wirte my sql so it only gives me the dictinct groups month/year  of the classes I have so that it comes out like so..
11/2006
12/2006
1/2007
3/2007
i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value.
dbo.classgiven.classdate date of the class.thank you all 
SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/"  + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode   FROM  dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND  (dbo.classT.coned IS NOT NULL)", conNorthwind )

View 9 Replies View Related

Reporting Services :: Displaying Sum Of Current Month And Year To Date Without Using Two Separate Datasets?

Jun 4, 2015

I am trying to use one dataset rather than two and was hoping to then filter the data via a table or matrix.

This is my dataset 

SELECT
Practice.ibvStaffCategorisation.StaffId
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableAmount) AS Sum_ChargeableAmount
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableAmount) AS Sum_NonChargeableAmount
,Practice.ibvStaffTotalsCL2Y.Period

[code]....

I would like to display two rows of data for each StaffId one representing the current period and the other all periods to date so the table would look something like below.

StaffId | Non Chargeable Time | Chargeable Time

CJJ | 0:20 | 4:20
     | 4:50 | 19:20
JN | 0:05 | 5:30
     | 1:30 | 25:30

The above shows two separate StaffId figures the first line for each shows non chargeable and chargeable for the current period and the second line a total of all periods in that year.

I have managed to get the first row to display only figures from the current period by using a filter however it also applies the same filter to the second row in the group. I have also tried to group the rows but am drawing a blank.

View 6 Replies View Related

Reporting Services :: Date Control Does Not Allow Double Click To Move By Month Or Year

Aug 21, 2015

I have a SSRS report developed in Visual Studio 2013 and using SQL server 2012. It has a date control based on parameters which works correctly in the development environment. I can select by day AND I can move from date to date by clicking on the month name to be able to move by month or double clicking to be able to move by year. 

When the report is deployed to Internet Explorer 11 or Firefox I can only, click back and forward by one day at a time and I can not double click the date control to move by month or double click to move by year. I can type the dates into the parameter box and get the correct result. I have set the compatibility view settings in IE for my server. 

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved