Need DateTime format and count of records

  • Hi all,

    I need small query which determines DateTime and count of records of a table.

    Example:

    Table1: this table has records like this:

    13/01/2010(DD/MM/YYYY)

    01/13/2010(MM/DD/YYYY)

    14/04/2001(DD/MM/YYYY)

    11/03/2002(MM/DD/YYYY)

    2010/01/01(YYYY/MM/DD)

    the result should be like this:

    datetime count

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

    DD/MM/YYYY 2

    MM/DD/YYYY 2

    YYYY/MM/DD 1

    Can any one help on this.

    Thank you all

    Waiting for your good response..

  • CREATE TABLE #Sample (MyStuff VARCHAR(22))

    INSERT INTO #Sample (MyStuff)

    SELECT '13/01/2010(DD/MM/YYYY)' UNION ALL

    SELECT '01/13/2010(MM/DD/YYYY)' UNION ALL

    SELECT '14/04/2001(DD/MM/YYYY)' UNION ALL

    SELECT '11/03/2002(MM/DD/YYYY)' UNION ALL

    SELECT '2010/01/01(YYYY/MM/DD)'

    SELECT RIGHT(MyStuff, 12), COUNT(*)

    FROM #Sample

    GROUP BY RIGHT(MyStuff, 12)

    “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

  • srisagar_p (10/25/2010)


    Hi all,

    I need small query which determines DateTime and count of records of a table.

    Example:

    Table1: this table has records like this:

    13/01/2010(DD/MM/YYYY)

    01/13/2010(MM/DD/YYYY)

    14/04/2001(DD/MM/YYYY)

    11/03/2002(MM/DD/YYYY)

    2010/01/01(YYYY/MM/DD)

    the result should be like this:

    datetime count

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

    DD/MM/YYYY 2

    MM/DD/YYYY 2

    YYYY/MM/DD 1

    Can any one help on this.

    Thank you all

    Waiting for your good response..

    Hi

    My understanding is

    This are the formates you have given, these things you will not store in the database

    MM/DD/YYYY or YYYY/MM/DD

    CREATE TABLE #Sample1 (MyStuff VARCHAR(22))

    INSERT INTO #Sample1 (MyStuff)

    SELECT '13/01/2010' UNION ALL

    SELECT '01/13/2010' UNION ALL

    SELECT '14/04/2001' UNION ALL

    SELECT '11/03/2002' UNION ALL

    SELECT '2010/01/01'

    Select MyStuff,case when RIGHT (left(MyStuff, 3),1) ='/' then 2 else 1 end [count] from #Sample1

    DROP TABLE #Sample1

    Thanks

    Parthi

    Thanks
    Parthi

Viewing 3 posts - 1 through 2 (of 2 total)

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