RE: Need DateTime format and count of records

SSCrazy Eights

Points: 9277

October 25, 2010 at 7:26 am

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..

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