Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Posts
    45

    Unanswered: Formating Sql Results

    In the following Query I would like the results to look like:

    2003/03/03 10PM

    Now it looks like:

    2003/03/03 10

    Is there any way to convert the 10 to a 10pm when its already part of an expression?

    SELECT CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp)) AS Expr1, count(*) FROM dbo.Transactions $WHERECLAUSE$ and type_id=74 GROUP BY CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp))

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    CONVERT(varchar(8), DATEPART(hh, Time_stamp),100)
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Dec 2002
    Posts
    45
    Originally posted by Satya
    CONVERT(varchar(8), DATEPART(hh, Time_stamp),100)

    changing:
    CONVERT(varchar(8), DATEPART(hh, Time_stamp))

    to:
    CONVERT(varchar(8), DATEPART(hh, Time_stamp), 100)

    Doesn't change the output at all?

  4. #4
    Join Date
    Apr 2003
    Posts
    18

    Re: Formating Sql Results

    Hi,

    Replace
    CONVERT(varchar(8), DATEPART(hh, Time_stamp),100)
    With
    Select substring ( Replace(convert(varchar, getdate(), 100), substring(convert(varchar, getdate(), 100), charindex(':', getdate()), 3), ''), 11, len(convert(varchar, getdate(),100)))

    i think give u the desire result.

    Cheers,
    Gola munjal



    Originally posted by Will trever
    In the following Query I would like the results to look like:

    2003/03/03 10PM

    Now it looks like:

    2003/03/03 10

    Is there any way to convert the 10 to a 10pm when its already part of an expression?

    SELECT CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp)) AS Expr1, count(*) FROM dbo.Transactions $WHERECLAUSE$ and type_id=74 GROUP BY CONVERT(varchar(8), DATEPART(yyyy, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(mm, Time_stamp)) + '/' + CONVERT(varchar(8), DATEPART(dd, Time_stamp)) + ' ' + CONVERT(varchar(8), DATEPART(hh, Time_stamp))

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Thanks for fine tuning....Gola
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    one more solution...


    select convert(varchar,getdate(),111) +
    ' ' +
    left(convert(varchar,getdate(),108),2) +
    right(convert(varchar,getdate(),100),2)
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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