October 25, 2010 at 6:19 am
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..
October 25, 2010 at 6:34 am
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)
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
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..
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