Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: Converting yyyymm to smalldatetime with last day of the month

    Hi all - How do I convert yyyymm to a smalldatetime format with the day set to last day of the month.

    Example: 200901 to 2009-01-31 00:00:00
    200902 to 2009-02-28 00:00:00

    I am able to convert the data to first day of the month. Example: 200901 to 2009-01-01 00:00:00. But I need the last day.

  2. #2
    Join Date
    Jul 2003
    Posts
    4

    Use DATEADD

    Hi Vivek,

    Use DATEADD to add a month and then to subtract a day.

    DATEADD(D,-1,DATEADD(M,1,CAST(@Yr + '-' + @Mo + '-' + '01' AS SmallDateTime))) AS Date3

  3. #3
    Join Date
    Apr 2007
    Posts
    183
    Code:
    DECLARE	@Sample INT
    
    SET	@Sample = 200901
    
    SELECT	DATEADD(MONTH, @Sample / 100 * 12 - 22800 + @Sample % 100, -1)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •