Cant format date for insert statement

• Jonathan Matt

  SSCarpal Tunnel

  Points: 4027

  More actions

  September 23, 2004 at 12:10 pm

  #59333

  Hello all,

  The following is cut from a stored procedure to insert a row in dbo.Periods:

  DECLARE @NEXT_YEAR int

  SET @NEXT_YEAR = ((SELECT MAX(Fiscal_Year) FROM dbo.Periods) + 1)

  IF (@type = 'Fiscal')

  BEGIN

  INSERT INTO dbo.Periods

   (

    Fiscal_Year,

    Period,

    Begin_Date,

    End_Date

  &nbsp

  VALUES

   (

    @NEXT_YEAR,

    1,

    '7/1/' + @NEXT_YEAR + '',

    '7/31/' + @NEXT_YEAR + ''

  &nbsp

  END

  Begin_Date and End_Date are both smalldatetime fields.

  I can't for the life of me figure out to format '7/1/ '+ @NEXT_YEAR + '' so that SQL Server recognizes it as '7/1/2006'.  No combination of single quotes works for me!

  Thanks

  Jonathan

• Jonathan Tomkins

  Ten Centuries

  Points: 1268

  More actions

  September 23, 2004 at 12:19 pm

  #524236

  try this...

  cast( cast(@NEXT_YEAR as char(4)) + '-07-01' as smalldatetime)

  if you make the date up using a region specific date format then it could go wrong at some point.

  jon

• Aaron Templeton

  SSCarpal Tunnel

  Points: 4655

  More actions

  September 23, 2004 at 12:22 pm

  #524238

  @NEXT_YEAR is an integer and causes string concatenation problems. CAST or CONVERT it to a varchar.

  '7/1/' + CONVERT(varchar, @NEXT_YEAR),

  '7/31/' + CONVERT(varchar, @NEXT_YEAR)

   

• Jonathan Matt

  SSCarpal Tunnel

  Points: 4027

  More actions

  September 23, 2004 at 12:30 pm

  #524239

  Thanks to both of you!

  '7/1/' + CONVERT(varchar, @NEXT_YEAR) works!

  Jonathan