Order by Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order by Date

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2010-01-17 : 12:00:04

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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-17 : 12:45:27

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.

Kristen
Test

22859 Posts

Posted - 2010-01-17 : 14:29:46

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.

bijayani
Starting Member

2 Posts

Posted - 2010-01-18 : 00:19:13

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

sanjay5219
Posting Yak Master

240 Posts

Posted - 2010-01-21 : 07:21:02

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.

sanjay5219
Posting Yak Master

240 Posts

Posted - 2010-01-21 : 07:34:31

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)

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 07:50:25

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 08:51:47

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

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 09:17:06

I would be inclined to do the GROUP BY on the inner SELECT ... I think ...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 10:16:55

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

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 10:42:12

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)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 10:57:15

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

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 11:38:36

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources