Date Format

  • Hi All,

    i have a table with following structure

    CREATE TABLE [dbo].[DateFormat](

    [WDate] [smalldatetime] NULL

    ) ON [PRIMARY]

    insert into DateFormat

    select '2010-06-18 07:33:17' union all

    select '2010-06-18 07:40:18' union all

    select '2010-06-18 18:40:10' union all

    select '2010-06-18 14:45:17' union all

    i need the result as follows

    Wdate

    18/06/2010 07:33:17 AM

    18/06/2010 07:40:18 AM

    18/06/2010 06:40:10 PM

    18/06/2010 02:45:17 PM

    I tried the following code

    SELECT CONVERT(varchar(12), WDate, 103) + ' ' + SUBSTRING(CONVERT(varchar(28), WDate, 9), 13, 15) AS Date

    FROM Dateformat

    i got the result as follows . but it's having the mi sec part like

    18/06/2010 07:33:17:000AM

  • I'm sure there must be a better way to do this, but try this: -

    SELECT CONVERT(VARCHAR, wdate, 103) + ' ' + Ltrim(

    Substring(CONVERT(VARCHAR, wdate, 100), 13, Len(wdate))) AS DATE

    FROM [Dateformat]


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Removed as the solution was incorrect..


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Kingston Dhasian (10/6/2010)


    Normally formatting such as these is done in the front end. Anyways this should help you out

    SELECT CONVERT(varchar(12), WDate, 103) + ' ' + CONVERT(varchar(28), WDate, 8) + ' ' +

    CASE WHEN DATEPART( HOUR, WDATE ) < 12 THEN 'AM' ELSE 'PM' END,

    CONVERT(varchar(28), WDate, 9)

    FROM Dateformat

    That wouldn't work Kingston, any PM time will show up as a 24 hour clock but with PM at the end, which is redundant.

    E.G.

    DECLARE @Var AS SMALLDATETIME

    SET @Var = '2010-06-18 17:33:17'

    SELECT CONVERT(varchar(12), @Var, 103) + ' ' + CONVERT(varchar(28), @Var, 8) + ' ' +

    CASE WHEN DATEPART( HOUR, @Var ) < 12 THEN 'AM' ELSE 'PM' END,

    CONVERT(varchar(28), @Var, 9)

    /*Returns

    -------------------------------------------- ----------------------------

    18/06/2010 17:33:00 PM Jun 18 2010 5:33:00:000PM

    */

    Whereas: -

    DECLARE @Var AS SMALLDATETIME

    SET @Var = '2010-06-18 17:33:17'

    SELECT CONVERT(VARCHAR, @Var, 103) + ' ' + Ltrim(

    Substring(CONVERT(VARCHAR, @Var, 100), 13, Len(@Var))),

    CONVERT(VARCHAR(28), @Var, 9)

    /*Returns

    ------------------------------------------------------------- ----------------------------

    18/06/2010 5:33PM Jun 18 2010 5:33:00:000PM

    */


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • skcadavre (10/6/2010)


    Kingston Dhasian (10/6/2010)


    Normally formatting such as these is done in the front end. Anyways this should help you out

    SELECT CONVERT(varchar(12), WDate, 103) + ' ' + CONVERT(varchar(28), WDate, 8) + ' ' +

    CASE WHEN DATEPART( HOUR, WDATE ) < 12 THEN 'AM' ELSE 'PM' END,

    CONVERT(varchar(28), WDate, 9)

    FROM Dateformat

    That wouldn't work Kingston, any PM time will show up as a 24 hour clock but with PM at the end, which is redundant.

    E.G.

    DECLARE @Var AS SMALLDATETIME

    SET @Var = '2010-06-18 17:33:17'

    SELECT CONVERT(varchar(12), @Var, 103) + ' ' + CONVERT(varchar(28), @Var, 8) + ' ' +

    CASE WHEN DATEPART( HOUR, @Var ) < 12 THEN 'AM' ELSE 'PM' END,

    CONVERT(varchar(28), @Var, 9)

    /*Returns

    -------------------------------------------- ----------------------------

    18/06/2010 17:33:00 PM Jun 18 2010 5:33:00:000PM

    */

    Whereas: -

    DECLARE @Var AS SMALLDATETIME

    SET @Var = '2010-06-18 17:33:17'

    SELECT CONVERT(VARCHAR, @Var, 103) + ' ' + Ltrim(

    Substring(CONVERT(VARCHAR, @Var, 100), 13, Len(@Var))),

    CONVERT(VARCHAR(28), @Var, 9)

    /*Returns

    ------------------------------------------------------------- ----------------------------

    18/06/2010 5:33PM Jun 18 2010 5:33:00:000PM

    */

    Oh. You are right. Your solution is the correct one then.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Kingston Dhasian (10/6/2010)


    Oh. You are right. Your solution is the correct one then.

    Still, it's a presentation layer task rather than a DB task, as you pointed out.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • LIYA (10/6/2010)


    insert into DateFormat

    select '2010-06-18 07:33:17' union all

    select '2010-06-18 07:40:18' union all

    select '2010-06-18 18:40:10' union all

    select '2010-06-18 14:45:17' union all

    i need the result as follows

    Wdate

    18/06/2010 07:33:17 AM

    18/06/2010 07:40:18 AM

    18/06/2010 06:40:10 AM

    18/06/2010 02:45:17 AM

    Can you confirm that you do actually want the last two rows to display "AM" and not "PM"?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • LIYA (10/6/2010)


    Hi All,

    i have a table with following structure

    CREATE TABLE [dbo].[DateFormat](

    [WDate] [smalldatetime] NULL

    ) ON [PRIMARY]

    insert into DateFormat

    select '2010-06-18 07:33:17' union all

    select '2010-06-18 07:40:18' union all

    select '2010-06-18 18:40:10' union all

    select '2010-06-18 14:45:17' union all

    i need the result as follows

    Wdate

    18/06/2010 07:33:17 AM

    18/06/2010 07:40:18 AM

    18/06/2010 06:40:10 AM

    18/06/2010 02:45:17 AM

    I tried the following code

    SELECT CONVERT(varchar(12), WDate, 103) + ' ' + SUBSTRING(CONVERT(varchar(28), WDate, 9), 13, 15) AS Date

    FROM Dateformat

    i got the result as follows . but it's having the mi sec part like

    18/06/2010 07:33:17:000AM

    Hi

    One thing dont forget that smalldatetime will not give you seconds so i have kept as datetime

    DECLARE @Var AS DATETIME

    SET @Var = '2010-06-18 17:33:23'

    Select Convert(varchar,@Var,103)+' '+Convert(varchar,Case when DATEPART( HOUR, @Var )>12 then DATEPART( HOUR, @Var )-12 else DATEPART( HOUR, @Var ) end )+':'+Convert(varchar,DATEPART( MINUTE, @Var ))+':'+Convert(varchar(5),DATEPART( SS, @Var ))

    + ' ' +CASE WHEN DATEPART( HOUR, @Var) > 12 THEN 'PM' ELSE 'AM' END

    Thanks

    Parthi

    Thanks
    Parthi

  • sorry Chris Morris-439714, it's PM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply