Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Aug 2014
    Posts
    31
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Question SQL Date Function

    Hi all,

    I have a DateTime field I would like to display as 'YYYY, MM', so '2015, 02' or '2014, 10'.

    I normally do this in excel with a formula, however, would like to include it in my source script.

    I'm trying to use:
    DATEPART(yyyy, [Appt Date]) + ', ' + Case When DatePart(mm, [Appt Date])<10 THEN '0' + DatePart(mm, [Appt Date]) ELSE DatePart(mm, [Appt Date]) END as 'Appt Month'

    But I get this:
    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value ', ' to data type int.

    I know a Level 16 error is one which can be repaired by the user, but I'm not sure where I'm going wrong to fix it.

    EDIT: I forgot to add, I'm using SQL 2008 r2, so CONCAT would not work for me unfortunately as a 2012 function

  2. #2
    Lounger
    Join Date
    Aug 2014
    Posts
    31
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Doesn't matter guys, this is solved now.

    For information, the working script line is:
    CAST(DATEPART(yyyy, [Appt Date]) AS CHAR(4)) + ', ' + Case When DatePart(mm, [Appt Date]) < 10 THEN '0' + CAST(DatePart(mm, [Appt Date]) AS CHAR(3)) ELSE CAST(DatePart(mm, Appt Date]) AS CHAR(4)) END as 'Appt Month'

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    San Francisco Bay Area
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is another way:
    select cast(datepart(yyyy,getdate())as varchar(4))
    +','+replicate('0',2-LEN(datepart(mm,getdate())))
    + cast(datepart(mm,getdate())as varchar(2))
    Regards,
    Bill Mosca, Microsoft Access MVP
    That&#39;ll do IT http://thatlldoit.com

Posting Permissions

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