none
SSRS Time Format AM vs. PM

    Question

  • Hi,

    I am looking to create a column which would pulle AM or PM based on the time field.  I've tried to trim the filed to just pull the last 2 characters which doesn't seem to work, but essentially I want to group based on the AM or PM column.

    I'm using Microsoft Visual Studio 2005

    Thanks,

    Kara


    • Edited by Kquattry Wednesday, April 24, 2013 6:46 PM
    Wednesday, April 24, 2013 6:39 PM

Answers

  • You have to convert it to a string first. I tried the below formula as a test and it returns either AM or PM depending on the time of day.

    =Right(CStr(Now), 2)

    If you have a datetime field in your dataset name dt that you want to group by am and pm, you could add the below formula to your Group by expression:

    =Right(FormatDateTime(Fields!dt.Value,3),2)

    FormatDateTime returns a string from the datetime value. The 3 specifies to convert the time element only. Once in a string format you can use Right to get the right 2 characters of the string.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Marked as answer by Kquattry Thursday, April 25, 2013 3:08 PM
    Wednesday, April 24, 2013 6:58 PM

All replies

  • You have to convert it to a string first. I tried the below formula as a test and it returns either AM or PM depending on the time of day.

    =Right(CStr(Now), 2)

    If you have a datetime field in your dataset name dt that you want to group by am and pm, you could add the below formula to your Group by expression:

    =Right(FormatDateTime(Fields!dt.Value,3),2)

    FormatDateTime returns a string from the datetime value. The 3 specifies to convert the time element only. Once in a string format you can use Right to get the right 2 characters of the string.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Marked as answer by Kquattry Thursday, April 25, 2013 3:08 PM
    Wednesday, April 24, 2013 6:58 PM
  • Hi Tim,

    That worked perfectly!  Thanks for the help.

    Kara

    Wednesday, April 24, 2013 8:42 PM
  • Glad to help. please mark appropriate post as answers so others with similar question can more easily find it.

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Wednesday, April 24, 2013 8:46 PM