SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Order by Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sanjay5219
Posting Yak Master

223 Posts

Posted - 01/17/2010 :  12:00:04  Show Profile  Reply with Quote
Dear All,

I am using one column entered_date as datetime and when i am using this column i have to use convert(char,entered_date,101) because it is reflecting 12:00:00.

Now my problem is how to user order by convert(char,entered_date,101)


My Query

Select convert(char,entered_date,101) as Entered_Date,Emp_Name,count(*) as Cnt from <Table Name>
group by convert(char,entered_date,101),Emp_Name

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 01/17/2010 :  12:45:27  Show Profile  Visit webfred's Homepage  Reply with Quote
So you want your order by without time part?
Try this:
order by DATEADD(day,DATEDIFF(day,0,entered_date),0)



No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/17/2010 :  14:29:46  Show Profile  Reply with Quote
It would be better if you reformatted your Date in your Application, rather than in SQL Server, otherwise (as you have discovered) you are converting a DateTime datatype to Char, and then it can no longer be considered as a Date/Time - for Sorting, or for any action you may want to perform in your Application itself that will want to treat the column as a Date/time.
Go to Top of Page

bijayani
Starting Member

India
2 Posts

Posted - 01/18/2010 :  00:19:13  Show Profile  Visit bijayani's Homepage  Reply with Quote
Hi,

Though I am not a technical person, I happened to see your post and wanted to share a link where a software engineer of my company has shared a Tip on SQL Server Date Time funtionality which is quite similar to your query.

I am sharing two links with you:

(spam removed)

(spam removed)

Hope you find it useful and of assistance.

Thanks,
Bijayani
Proud to be a part of Team Mindfire!
Mindfire: India's Only Company to be both Apple Premier & Microsoft Gold certified.
(spam removed)

Regards
Go to Top of Page

sanjay5219
Posting Yak Master

223 Posts

Posted - 01/21/2010 :  07:21:02  Show Profile  Reply with Quote
I have tried this but getting an error
Server: Msg 8127, Level 16, State 1, Line 1
Column name 'DATA_INPUT.ENTERED_DATE' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Go to Top of Page

sanjay5219
Posting Yak Master

223 Posts

Posted - 01/21/2010 :  07:34:31  Show Profile  Reply with Quote
My query
Select convert(char,entered_date,101),count(*) as dd from INPUT
group by convert(char,entered_date,101)
order by DATEADD(day,DATEDIFF(day,0,entered_date),0)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/21/2010 :  07:50:25  Show Profile  Reply with Quote
You are trying to ORDER BY using [entered_date], but that does not exist in the SELECT statement anymore - instead our have convert(char,entered_date,101) in your SELECT.

As I said earlier, if you reformat your date to a string you get all sorts of other knock on problems, this is one of them

You now have a String date in your SELECT statement. You can sort by that, but because it is in mm/dd/yyyy format that isn't going to help much.

You could convert it back to datetime in the ORDER BY so you can then manipulate it (chronologically) but its all getting very messy.

You may be able to do this:

Select convert(char,entered_date,101),count(*) as dd from INPUT
group by entered_date
order by DATEADD(day,DATEDIFF(day,0,entered_date),0)

i.e. order by the underlying date, convert it to mm/dd/yyyy in your SELECT an order by the date, excluding TIME ...

or if that doesn't work this:

Select convert(char,DATEADD(day,DATEDIFF(day,0,entered_date),0),101),count(*) as dd from INPUT
group by DATEADD(day,DATEDIFF(day,0,entered_date),0)
order by DATEADD(day,DATEDIFF(day,0,entered_date),0)

so you are using the same date expression for GROUP BY that you then use everywhere else.

Much better would be to format the date in the application ...
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/21/2010 :  08:51:47  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
I 100% agree. Don't screw with the dates in the database layer if you can help it.

If you are hell bent on losing precision though, this will probably work

SELECT
	i.[entered_date]
	, COUNT(i.[entered_date]) AS [dd]
FROM
	(
	SELECT
		DATEADD(DATEDIFF(DAY, 0, [entered_date]), 0) AS [entered_date]
	FROM
		INPUT
	)
	i
GROUP BY
	i.[entered_date]
ORDER BY
	i.[entered_date]



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Edited by - Transact Charlie on 01/21/2010 08:52:03
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/21/2010 :  09:17:06  Show Profile  Reply with Quote
I would be inclined to do the GROUP BY on the inner SELECT ... I think ...
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/21/2010 :  10:16:55  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
I'm never sure about that or not. If you did do a group by then you'd be evaluating DATEADD(DATEDIFF(DAY......) etc. At least twice.

If you wanted to do it in a one'er with SELECT, GROUP BY and ORDER BY all with the DATEADD..... I just don't know if that is cached and only done once or if there is overhead at each step of the SELECT, GROUP BY and ORDER BY...

Either way. using this completely kills any opportunity to use an index so meh.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/21/2010 :  10:42:12  Show Profile  Reply with Quote
I ran it on 1,697,097 rows, which gave 8 distinct days.

No discernible difference, even taking the outer "formatting" wrapper away. GTried both ROUP BY inside the inner, and in the outside wrapper. I did have an index on the [entered_date] column.

The table was having rows added to it as I ran the tests, but I got:

Scan count 5, logical reads 5104

Logical reads had gone up by another half-a-dozen by the time I finished the last test, I reckon that was the table increasing in size rather than any significant difference in query plan (my last test was with the outer formatting wrapper removed, hard to image that that would need more I/O's!

(This was SQL 2008)
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/21/2010 :  10:57:15  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
That's good to know.

Was the query plan any different?




Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/21/2010 :  11:38:36  Show Profile  Reply with Quote
There were some fiddly bits with date ranges that I couldn't be bothered to look at closely, TBH. But other than that they looked the same.

SELECT	I.[MyDate] AS I,
	COUNT(I.[MyDate]) AS [dd]
FROM
(
	SELECT	DATEADD(Day, DATEDIFF(Day, 0, [MyDate]), 0) AS [MyDate]
	FROM	dbo.MyTable
) AS I
GROUP BY I.[MyDate]
ORDER BY I.[MyDate]

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[globalagg1007],0)))
       |--Parallelism(Gather Streams, ORDER BY:([Expr1003] ASC))
            |--Stream Aggregate(GROUP BY:([Expr1003]) DEFINE:([globalagg1007]=SUM([partialagg1006])))
                 |--Sort(ORDER BY:([Expr1003] ASC))
                      |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1003]))
                           |--Hash Match(Partial Aggregate, HASH:([Expr1003]), RESIDUAL:([Expr1003] = [Expr1003]) DEFINE:([partialagg1006]=COUNT([Expr1005])))
                                |--Compute Scalar(DEFINE:([Expr1003]=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',[MyDatabase].[dbo].[MyTable].[MyDate]),'1900-01-01 00:00:00.000'), [Expr1005]=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',[MyDatabase].[dbo].[MyTable].[MyDate]),'1900-01-01 00:00:00.000')))
                                     |--Index Scan(OBJECT:([MyDatabase].[dbo].[MyTable].[MyDateIndex]))


Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTable'. Scan count 5, logical reads 5104, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 967 ms,  elapsed time = 271 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

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

SELECT	I.[MyDate] AS I,
	[T_Count] AS [dd]
FROM
(
	SELECT	DATEADD(Day, DATEDIFF(Day, 0, [MyDate]), 0) AS [MyDate],
		COUNT(*) AS [T_Count]
	FROM	dbo.MyTable AS I
	GROUP BY DATEADD(Day, DATEDIFF(Day, 0, [MyDate]), 0)
) AS I
-- GROUP BY I.[MyDate]
ORDER BY I.[MyDate]

  |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[globalagg1005],0)))
       |--Parallelism(Gather Streams, ORDER BY:([Expr1002] ASC))
            |--Stream Aggregate(GROUP BY:([Expr1002]) DEFINE:([globalagg1005]=SUM([partialagg1004])))
                 |--Sort(ORDER BY:([Expr1002] ASC))
                      |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1002]))
                           |--Hash Match(Partial Aggregate, HASH:([Expr1002]), RESIDUAL:([Expr1002] = [Expr1002]) DEFINE:([partialagg1004]=COUNT(*)))
                                |--Compute Scalar(DEFINE:([Expr1002]=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',[MyDatabase].[dbo].[MyTable].[MyDate] as [I].[MyDate]),'1900-01-01 00:00:00.000')))
                                     |--Index Scan(OBJECT:([MyDatabase].[dbo].[MyTable].[MyDateIndex] AS [I]))

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTable'. Scan count 5, logical reads 5110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 764 ms,  elapsed time = 222 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

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

SELECT	DATEADD(Day, DATEDIFF(Day, 0, [MyDate]), 0) AS [MyDate],
	COUNT(*) AS [T_Count]
FROM	dbo.MyTable AS I
GROUP BY DATEADD(Day, DATEDIFF(Day, 0, [MyDate]), 0)
ORDER BY [MyDate]

  |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[globalagg1005],0)))
       |--Parallelism(Gather Streams, ORDER BY:([Expr1002] ASC))
            |--Stream Aggregate(GROUP BY:([Expr1002]) DEFINE:([globalagg1005]=SUM([partialagg1004])))
                 |--Sort(ORDER BY:([Expr1002] ASC))
                      |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1002]))
                           |--Hash Match(Partial Aggregate, HASH:([Expr1002]), RESIDUAL:([Expr1002] = [Expr1002]) DEFINE:([partialagg1004]=COUNT(*)))
                                |--Compute Scalar(DEFINE:([Expr1002]=dateadd(day,datediff(day,'1900-01-01 00:00:00.000',[MyDatabase].[dbo].[MyTable].[MyDate] as [I].[MyDate]),'1900-01-01 00:00:00.000')))
                                     |--Index Scan(OBJECT:([MyDatabase].[dbo].[MyTable].[MyDateIndex] AS [I]))

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTable'. Scan count 5, logical reads 5113, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 748 ms,  elapsed time = 216 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Edited by - Kristen on 01/21/2010 11:39:06
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000