SQL Server Performance

Date in mm/dd/yy hh:mm:ss AM/PM format

Discussion in 'General Developer Questions' started by amitm79, Jul 18, 2004.

  1. amitm79 New Member

    Hi All,<br />Does anybody have an idea how to convert a date into format mm/dd/yy hh:mm<img src='/community/emoticons/emotion-7.gif' alt=':s' />s AM/PM? Its a mix of 101 & 108 format specifier coupled with AM/PM. An example is<br /><br />Source: 2004-07-19 13:15:45<br /><br />Destination: 07/19/2004 01:15:45 PM<br /><br /><br />Regards,<br />amitm79
  2. satya Moderator

  3. amitm79 New Member

  4. Chappy New Member


    CREATE FUNCTION dbo.formatDateTime(@d datetime)
    RETURNS varchar(20)
    AS
    BEGIN
    RETURN
    CONVERT(varchar, @d, 101) + ' ' +
    SUBSTRING(CONVERT(varchar, @d, 100), 13, 5) + ' ' +
    SUBSTRING(CONVERT(varchar, @d, 100), 18, 2)
    END
    GO

    select dbo.formatDateTime(GETDATE())
  5. derrickleggett New Member

    Won't SELECT CONVERT(VARCHAR(@date,22) do this for you???

    Here is a query that gives you ALL the date CONVERT formats as an fyi. If it's not in this list, then you have to make a custom function for it.



    SET NOCOUNT ON

    DECLARE
    @min INT,
    @max INT,
    @date DATETIME

    SELECT
    @min = 1,
    @max = 131,
    @date = GETDATE()

    SELECT @date

    WHILE @min <= @max
    BEGIN

    IF @min BETWEEN 15 AND 19
    OR @min = 26
    OR @min BETWEEN 27 AND 99
    OR @min BETWEEN 115 AND 119
    OR @min BETWEEN 122 AND 125
    OR @min BETWEEN 127 AND 129
    BEGIN
    GOTO NEXT_LOOP
    END

    SELECT @min, CONVERT(VARCHAR,@date,@min)

    NEXT_LOOP:

    SELECT @min = @min + 1
    END

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  6. Chappy New Member

    Nearly, but not quite as format 22 uses 2 digit years.

    Also you might consider sending the datetime back as a datetime datatype, and format it in your presentation layer

  7. derrickleggett New Member

    I just noticed after you posted that. He has this in the first sentence: mm/dd/yy hh:mm<img src='/community/emoticons/emotion-7.gif' alt=':s' />s AM/PM<br />Then his example is mm/dd/yyyy hh:mm<img src='/community/emoticons/emotion-7.gif' alt=':s' />s AM/PM<br /><br />I don't know why they didn't carry the CONVERT to it's logical conclusion and give a 1xx version of all the xx versions. Oh well.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  8. Chappy New Member

    Yeah I agree, it would also be nice to have a format string you could define yourself 'dd mmm yyyy'. Still, we cant have everything I guess ;-(
  9. amitm79 New Member

    Hey Guys,
    Thanks for all the inputs. I could do fine with Chappy's idea except that i changed it to 101, 108 and 100 format specifiers in the three queries. Even i agree that we should be able to format dates based on the specifiers we choose in some specified syntax. Anyways, workarounds are always there and together, we can find them out.

    Regards,
    amitm79

Share This Page