Breaking a date into multiple parts

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  January 25, 2007 at 4:49 am

  #117081

  How can I break a date range into multiple parts?

  Here is the DDL for it....

   

  IF OBJECT_ID('tempdb..#myTable') IS NOT NULL

         DROP TABLE #myTable

   

  CREATE TABLE #myTable

  (

         RecordID int identity(1,1) not null primary key clustered,

         EmpID int not null, 

         Sal    varchar(10) not null,

         PeriodFrom smalldatetime not null,

         PeriodTo smalldatetime not null,

  )

   

  INSERT INTO   #myTable( EmpID, Sal, PeriodFrom, PeriodTo )

  SELECT 1, 'PAY_1', '20060101', '20060114'

  UNION ALL

  SELECT 1, 'PAY_1', '20060115', '20060331'

   

  SELECT * FROM #myTable ORDER BY EmpID, Sal, PeriodFrom

   

  IF OBJECT_ID('tempdb..#myTable') IS NOT NULL

         DROP TABLE #myTable

   

  I need the break up of these periods for each month...i.e

  The first record which is from 1-Jan-2006 to 14-Jan-2006 will stay the same is it falls in the same month.  Now the second record i.e. 15-Jan-2006 to 31-Mar-2006 which spans over 2 months. For this, I need to break this period for each month...

  15-Jan-2006 to 31-Jan-2006

  1-Feb-2006 to 28-Feb-2006

  1-Mar-2006 to 31-Mar-2006

   

  --Ramesh
  

  ---

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  January 25, 2007 at 7:04 am

  #685512

  Join to CALENDAR table.

  _____________
  Code for TallyGenerator