# SQL Server 2012 :: Number Of Months Between Two Dates In YYYYMM Format

Jun 15, 2015

I am looking to calculate no of months between two dates which are in YYYYMM format.

Like no of months between 201505 and 201305

## SQL Server 2008 :: Calculate Number Of Months Between Dates For Multiple Records?

Oct 7, 2015

I have a challenge and I'm not sure the best route to go. Consider the following dataset.

I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.

CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012

What I want to know is the average number of months between the purchases. And do this for each customer.

## Nb Months Between 2 Dates (one In DB2 Format)

Jan 19, 2004

How can I get the number of months (or years or days...)
between the current_timestamp
an a date in the DB2 format ?

DateDB2 Char(26) = 2000-12-30-12.55.30.123456
DateSQL = GetDate() or Current_Timestamp

## Transact SQL :: Find Missing Months In A Table For The Earliest And Latest Start Dates Per ID Number?

Aug 27, 2015

I need to find the missing months in a table for the earliest and latest start dates per ID_No.  As an example:

create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth)
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'

For the above table, the answer should be:

ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01

1) don't include an ID column,

2) don't use the start date/end dates in the data or

3) use cursors, which are forbidden in my environment.

## Subtracting Periods That Are In YYYYMM Format?

Mar 28, 2014

I have a Column in my data that gives a financial period value in the YYYYMM format. i.e. an asset was re-valued in a particular period for example 201301. I need to find out the number(count) of periods(months) between another given period for example current period (201403) and the period provided in the table i.e. 201301.

Is this possible in the SQL Database?

## SQL Server 2012 :: Split Difference (number Of Nights) Between 2 Dates Into Respective Month Column

Sep 23, 2014

I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.

Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:

I need my query to output in the following format:

How to write this query?

## Months Between 2 Dates

May 24, 2008

Hi Friends,

i need a Month name between 2 dates...

Example :

If i give start date(MM/dd/yyyy) is 03/20/2008
and End Date(MM/dd/yyyy) is 05/24/2008)

The Output Should be

March,
April,
May

How can i get this...

Thank you...

## Get Last Day Of Each Month For Months Between 2 Dates

Sep 14, 2012

I'm trying to write some code that will get me the last day of each month for the months that fall between 2 dates.

So if i have a table with a record with a begin date of 01-01-2012 and an end date of 09-14-2012, i would want a result set of the following 9 records:

01-31-2012
02-29-2012
03-31-2012
04-30-2012
05-31-2012
06-30-2012
07-31-2012
08-31-2012
09-30-2012

So i basically get the last day of each month between the dates listed, including the months that the dates are in themselves (inclusive list).

I have a date dimension table with 1 row for every day from 1990 to 2025. There is also a field with the Last day of Month for a given date. My issue is that I'm not sure how to write the T-SQL to get what I need.

## Delete Dates &> 6 Months

May 6, 2004

Hello,

I have created a Table Called Log in SQL Server 7. I use this table as you can guess to log all transaction performed in my program. However, I would like my program to run an SQL Statement that will flush all entries in the table Log that are 6 months old from today's date.

I am unable to find the statement that will execute this operation.

Thanks

Mark

## Find Months Between 2 Dates

Apr 2, 2008

hai friends,

I have a problem in finding month and year sequence between to date. one date is minimum date for example march 2005 and one maximum date is today's date april 2008. I need output of month between these years like this

March 2005

April 2005

May 2005

......

January 2006

...

December 2007

January 2008

.....
April 2008

Can any one help me to develop a stored procedure to produce this output...

Abilina

## SQL Server 2012 :: Divide Amount Between Months

Mar 18, 2015

Background:

Void Start Date: When a property becomes empty or vacant

Let Date: When the property is filled in again

I have the sample data below and like to show the void loss per month basis as below:

1) Allocate the amount from voidloss column between months based on voiddays:

for example for propcode 3698 the amount 13,612.56 needs to be divided between September and October based on the VoidDays. i.e of the 39 voiddays, 25 where in September and 14 in October hence 8726 will be allocated to September and 4886.56 to October

2) After allocating the amount sum the amount by controlgroup and total the voiddays per month. It will be great if we can divide the voiddays between months and sum them by controlgroup as well.

So in the end result we should have

ControlGroup, Month, Year, VoidLoss, VoidsdayinMonth
106 September 2014 8726 25
106 October 2014 4886.56 14
106 December 2014 2940 7

Declare @voidloss Table
( History_IND INT
,PropCode VARCHAR(10)
,VoidCategory VARCHAR(10)
,ControlGroup VARCHAR(10)
,VoidStartDate date

[Code] .....

## Query For Getting Missing Dates In Months

Feb 5, 2008

I have a table like FK_ID, Value, Date (here FK_ID is  foreign key)this table getting updated frequently by daily bases that means one record per one day(For example in January month it has maximum 31 records or minimum 0 records, in February it has maximum 28 or 29 or minimum 0 records, based on calender year)I need to query this table to get missing dates in between particular monthsfor example for one FK_ID has only 25 records in Jan 2008 month and in Feb 2008 it has 10 records , so i need to get those missing 6 dates from JAN month and 18 dates from FEB monthhow can i query this

## Return All Months Within A Range Of Dates

Jun 18, 2004

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?

## Getdate() To Select Dates Less Than 15 Months

Apr 7, 2008

Hi,
I need help. I want to select dates less than 15 months, as i am new to sql server , can anyone advise me.

I tried this query but it gave me the dates that are greater than 15 months

Selec calendardate from table
where calendardate < getdate()-457

Thanks

## SQL Server 2012 :: Missing Months In A GROUP BY Statement

Jan 20, 2015

I am trying to get a count by product, month, year even if there are is no record for that particular month.

Current outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ April 2014 34
...

Desired outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ March 2014 0
XYZ April 2014 34
...

The join statement is simple:
Select Product, Month, Year, Count(*) As Count
From dbo.Products
Group By Product, Month, Year

I have also tried the following code and left joining it with my main query but the product is left out as is seen:

DECLARE @Start DATETIME, @End DATETIME;
SELECT @StartDate = '20140101', @EndDate = '20141231';
WITH dt(dt) AS
(
FROM ( SELECT TOP (DATEDIFF(MONTH, @Start, @End) + 1)
n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
FROM sys.all_objects ORDER BY [object_id] ) AS n
)

2nd attempt:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
NULL March 2014 0
XYZ April 2014 34
...

What I want is this (as is shown above). Is this possible?

Desired outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ March 2014 0
XYZ April 2014 34
...

## SQL Server 2012 :: Delete XML Data Older Than 2 Months

Aug 18, 2015

I have a table which has XML data, so it takes lot of time to delete it.

Here's the condition:

Archive all data older than 2 months and delete it.

What would be the good approach for this?

## SQL Server 2012 :: Generate Months Based On Previous Values

Jul 7, 2015

I have a data that with month values ranging from jan 2012 till july 2013 with some values associated with it.

I want to generate months automatically after july 2013 till december 2013 in sql something like the below one:

Is there a way in sql to do this?

## How To Find Next 6 Months Dates Basing On Current Date

Jan 28, 2015

I need to find out All mondays / Tuesdays etc dates for next 6 months from the current date.

I have a table called DayOfWeek and the records are

1Sun
2Mon
3Tue
4Wed
5Thu
6Fri
7Sat

so if I pass "1" to the stored procedure, I need to find all Sunday dates for the next 6 months

And so if I pass "2" to the stored procedure, I need to find all Monday dates for the next 6 months...etc

## T-SQL (SS2K8) :: Pass Dates For Previous Months Start And End Date

Mar 16, 2015

I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1

## SQL Server 2012 :: Select Query - Get Result As Month And Values For All Months Whether Or Not Data Exists

Jul 27, 2015

I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.

The Similar table would be-

create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)

But in result i want the table as -

Month Value

Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull

## Add A Number Of Months To Date

Oct 15, 2014

I have a simple query which returns a purchase date plus the number of months warranty an item has. What I need to do is add these months to the purchase date to get a warranty expiry date. What is the best way to do this? My query and results are below, I need an extra column in the results with that calculated warranty date:

select item, purchase_date, warranty_period
from inventory where item like '05MC%'

item purchase_date warranty_period
-------------------- ----------------------- ---------------
05MC0001 2014-07-22 00:00:00 36
05MC0002 2014-07-11 00:00:00 36

## Trying To Get A Series Of Number To Go By Months

Jul 12, 2007

if

(datalength

(cast_varchar

([Costpoint].[Profit_Calc_Consolidation].[PD])

) = 1)

then

(0 ||

cast_varchar

([Costpoint].[Profit_Calc_Consolidation].[PD]))

else

(cast_varchar

([Costpoint].[Profit_Calc_Consolidation].[PD]))

Goal:

To be able to tell the Period number ([PD]) to change from an integer to a var_char so that it can be concatenated with the Fiscal Year. I.e. Fiscal Year || Period number to produce the following results.

2006 01

2006 02

2006 03

Etc……

Status of Problem:

The issue is that I can only get it done to the point to where the Fiscal Year || Period number yields the following results:

20061

200610

200611

200612

20062

Etc….

This is as issue since I am trying to get all the information from the past to a certain year and period. Using a filter that says

Fiscal Year + Period Number <= ?Prompt_Fiscal_Year? + ?Prompt_Period_Number?

So if I enter Year: 2007 Period 3

I should get:

2006 01

2006 02

2006 03

Etc…….

2006 11

2006 12

2007 01

2007 02

2007 03

Can you please let me know if my thinking is off or if my SQL is wrong, Thank you for your assistance in this matter.

## SQL Server 2012 :: Calendar Style Report With 12 Months (as Columns) And Store Opening Listing In Rows

Oct 16, 2014

I am trying to create a calendar style report that will have 12 months (as columns) and store opening listing in rows. I have created a matrix, but the problem that I have is that the store opening listing displays in the right period, but they are not in any order. I would like to have the openings always on top, right under the header in the matrix. Now I have them scattered randomly all over the matrix. I tried numerous way of sorting and that does not work.

I am attaching a sample of what I would like to accomplish (months are columns).

## SQL Server 2008 :: Converting SAS Dates In A Float Format To Datetime Values

Mar 17, 2015

I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.

## Calculate Number Of Admits In Past 12 Months

Aug 5, 2013

I have a report that has 41 fields one of these fields is the Admit Date. I need to create a field that shows a count of Admits in the past 12 months .

## Transact SQL :: Number Of Distinct Users In Last 3 / 6 / 9 / 12 Months

Aug 26, 2015

I have table A(year int, month int, user varchar(20)), and I am trying to write a view that would show number of distinct users in the last 3 months, last 6 months, last 9 months and last 12 months(all from the most recent year/month) in following format:

3 months     6 months     9 months     12  months

number of distinct users        x                  y                  z                      w

## How To Change A Decimal Number To Percent Format Number?

Oct 8, 2006

in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?

thks

## SQL Server 2012 :: Dates In Different Languages

Mar 31, 2014

Is there a way to make a single insert (in a loop) and take system dates and insert them in different languages without making a new loop for each language.

Example

Create table dateLanguage(
monthName nvarchar(20),
monthNameEs nvarchar(20),
monthNameFr nvarchar(20),
MonthNamePt nvarchar(20)
)

So for example
January,
Enero,
Janvier,
janeiro

## Power Pivot :: Revenue Schedules - Creating Rows By Number Of Months

May 26, 2015

I am trying to calculate how much revenue we may get, based on potential new business opportunities. The core fields we have are

Total Contract Value (\$ or £)Duration of contract (months)Revenue start dateVarious information about the new business - ID, Title, Customer etc.

We can easily calculate the revenue per month with "Total Contract value divide by duration".

However what I would really like to do is be able to know how much revenue we will be getting each month.

To do this I was thinking we should probably create a new row for each month entry, with the mm-yyyy being the only difference for each row. But how to create the appropriate months and the correct amount of rows.

## SQL Server 2012 :: MIN And MAX Dates For Groups Of Rows?

Jan 21, 2014

I have a dataset that contains an EmployeeID, StartDate, EndDate, and Location. Each record tells me at which location and employee was during a payroll period (fortnightly). So the data looks like:

EMP_KEYSTART_DTEND_DTLOCATION
120130117201301318103
120130117201301318103
120130131201302143354
120130131201302148103
220130117201301311234
220130131201302144567
120130214201302283354
220130214201302281234

Employees can be at multiple locations during the two weeks. They can work at one location, stop working there, start working somewhere else, and then maybe go back to their old location. There are duplicate records here as each employee can be at the same location multiple times during the two week period. What I need to capture is the actual start and end date of an employee at each location for each 'assignment'. An assignment is defined as a continual period of employment at a location with gaps of no less than 4 days - if there is a gap of more than four days then that is classed as a new assignment.

## SQL Server 2012 :: Getting End Dates For History Segments?

Aug 14, 2014

I have a customer history table with the follow structure and data:

CustomerID Tier StartDate RecordStatus

123 A 01/01/2013

## SQL Server 2012 :: Getting End Dates For History Segments

Aug 14, 2014

I have a customer history table with the follow structure and data:

CustomerID Tier StartDate RecordStatus

123 A 01/01/2013 1
123 A 03/01/2013 0
123 B 03/01/2013 1
123 B 06/01/2013 0
123 A 08/01/2014 1
456 C 02/01/2014 1

CREATE TABLE TEMPHISTORY(
CUSTOMERID VARCHAR(11),
TIER VARCHAR(10),
STARTDATE DATE,
RECORDSTATUS TINYINT)

[Code] .....

The RECORDSTATUS value of 1 means the record is active. A corresponding record of the same CustomerID, Tier. in startdate chronology, with a value of 0 indicates that the previous record with the status of 1 has now terminated and the startdate of the record with recordstatus of 0 is the start date of the termination of the previous record, or better stated, the end date of the previous record.

What I need to do is re-record the above data the startdate of each terminated record become an enddate for the previous record, minus 1 day, as follows:

CUSTOMERID TIER STARTDATE ENDDATE
123 A 01/01/2013 02/28/2013
123 B 03/01/2013 05/31/2013
123 A 08/01/2014 NULL
456 C 02/01/2014 NULL

## SQL Server 2012 :: List Dates In Columns

Nov 11, 2014

I have a table (Event_Table) like:

EmployeeID, CustomerID, Date
1, 11, 2014-11-11
2, 13, 2014-12-10
1, 11, 2014-12-21
2, 13, 2015-01-11
1, 11, 2015-03-02

And now I would like to have a summary with a unique Employee/Customer combination and 3 Date columns like:

EmployeeID, CustomerID, Date1, Date2, Date3
1, 11, 2014-11-11, 2014-12-21, 2015-03-02
2, 13, 2014-12-10, 2015-01-11

Dates should be arranged with the first date in Date1, the next in Date2 and the third in Date3 (if there are forth and more dates I don´t care)