Comparing Dates - using DateADD features

Question

Comparing Dates - using DateADD features

rhunt

SSCertifiable

Points: 5868
More actions
February 1, 2005 at 9:30 am

#164452

I have a data integration process that moves alot of data around on servers. Accordinlgy, I keep 5 days worth of local table copies for two of our most pressing tables. One PROC in my process is giving me trouble - most definitely because I have limited experience with manipulating dates.
My current proc works perfectly, EXCEPT for the last day of the month. For instance, last night's table backup was stored as [TableName_02_31_05] instead of [TableName_01_31_05]
Can someone look at my proc and make suggestions on how to do this better?
__________________________________________________________
CREATE PROCEDURE spCreateBuildingsTableBackup AS
declare @strSQL VARCHAR(1000),
@sDate VARCHAR(11),
@sDay varchar(2),
@sMonth varchar(2),
@sYear varchar(4)
SET @sMonth =
CASE
    WHEN CAST(MONTH(GETDATE()) AS VARCHAR(2)) <= 9 THEN '0' + CAST(MONTH(GETDATE()) AS VARCHAR(2))
    WHEN CAST(MONTH(GETDATE()) AS VARCHAR(2)) > 9 THEN CAST(MONTH(GETDATE()) AS VARCHAR(2))
END
SET @sDay =
CASE
    WHEN CAST(Day(DATEADD(DD, -1, (GETDATE()))) as varchar(2)) <= 9 THEN '0' + CAST(Day(DATEADD(DD, -1, (GETDATE()))) as varchar(2))
    WHEN CAST(Day(DATEADD(DD, -1, (GETDATE()))) as varchar(2)) > 9 THEN CAST(Day(DATEADD(DD, -1, (GETDATE()))) as varchar(2))
END
SET @sYear = Cast(YEAR(GETDATE()) as varchar(4))
set @sDate = @sMonth + '_' + @sDay + '_' + @sYear
set @strSQL = 'Create TABLE [dbo].[BuildingsBackup' + '_' + @sDate + '] ('
set @strSQL = @strSQL + '[BuildingID] [uniqueidentifier] NOT NULL ,
[BuildingNumber] [int] NOT NULL ,
[BuildingName] [nvarchar] (160) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Division] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Agent] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BuildingType] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BuildingSqft] [int] NULL ,
[Address] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[State] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Active] [bit] NOT NULL ,
[JointVentureApproval] [bit] NULL ,
[OperatingExpensesPSF] [money] NULL ,
[FixedVariablePercentage] [real] NULL ,
[CreatedDate] [datetime] NULL ,
[CreatedBy] [uniqueidentifier] NULL ,
[ModifiedDate] [datetime] NULL ,
[ModifiedBy] [uniqueidentifier] NULL
) ON [PRIMARY]'
EXEC(@strSQL)
--PRINT(@strSQL)
--select @sDate, @sMonth, @sDay, @sYear
GO

Thanks
Ryan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I think this will work. I have replaced GETDATE() with @TestDate so you can try different dates to make sure this works.

DECLARE @strSQL varchar(1000),

@TestDate smalldatetime,

@sDate varchar(11),

@sDay varchar(2),

@sMonth varchar(2),

@sYear varchar(4)

SELECT @TestDate = '03/01/2005'

SET @sMonth =

CASE

WHEN CAST( month( @TestDate) AS varchar(2)) <= 9 THEN '0' + CAST( month( DATEADD( mm, -1, @TestDate)) AS varchar(2))

WHEN CAST( month( @TestDate) AS varchar(2)) > 9 THEN CAST( month( DATEADD( mm, -1, @TestDate)) AS varchar(2))

END

SET @sDay =

CASE

WHEN CAST( day( DATEADD(dd, -1, ( @TestDate))) AS varchar(2)) <= 9 THEN '0' + CAST( day( DATEADD(dd, -1, ( @TestDate))) AS varchar(2))

WHEN CAST( day( DATEADD(dd, -1, ( @TestDate))) AS varchar(2)) > 9 THEN CAST( day( DATEADD(dd, -1, ( @TestDate))) AS varchar(2))

END

SET @sYear = CAST( year( @TestDate) AS varchar(4))

SET @sDate = @sMonth + '_' + @sDay + '_' + @sYear

SELECT @sDate

I wasn't born stupid - I had to study.

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

This might be quicker.

SELECT SUBSTRING( CONVERT( varchar, DATEADD( dd, -1, GETDATE()), 101), 1, 2) + '_' +

SUBSTRING( CONVERT( varchar, DATEADD( dd, -1, GETDATE()), 101), 4, 2) + '_' +

SUBSTRING( CONVERT( varchar, DATEADD( dd, -1, GETDATE()), 101), 7, 4)

I wasn't born stupid - I had to study.

rhunt SSCertifiable Points: 5868 More actions · Answer 3

Farrell,

SELECT SUBSTRING( CONVERT( varchar, DATEADD( dd, -1, GETDATE()), 101), 1, 2) + '_' +

SUBSTRING( CONVERT( varchar, DATEADD( dd, -1, GETDATE()), 101), 4, 2) + '_' +

SUBSTRING( CONVERT( varchar, DATEADD( dd, -1, GETDATE()), 101), 7, 4)

works perfectly! Looking at it, I'm not sure why I wasn't doing this before.

Thanks very much.

Ryan