| Author |
 Topic  |
|
|
sanjay5219
Posting Yak Master
178 Posts |
 Posted - 01/17/2010 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 01/17/2010 : 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
United Kingdom
22191 Posts |
Posted - 01/17/2010 : 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
India
2 Posts |
Posted - 01/18/2010 : 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
178 Posts |
Posted - 01/21/2010 : 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
178 Posts |
Posted - 01/21/2010 : 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
United Kingdom
22191 Posts |
Posted - 01/21/2010 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3441 Posts |
Posted - 01/21/2010 : 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 |
Edited by - Transact Charlie on 01/21/2010 08:52:03 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 01/21/2010 : 09:17:06
|
| I would be inclined to do the GROUP BY on the inner SELECT ... I think ... |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3441 Posts |
Posted - 01/21/2010 : 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
United Kingdom
22191 Posts |
Posted - 01/21/2010 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3441 Posts |
Posted - 01/21/2010 : 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
United Kingdom
22191 Posts |
Posted - 01/21/2010 : 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.
|
Edited by - Kristen on 01/21/2010 11:39:06 |
|
|
| |
Topic |
|
|
|
Order by Date
