October 25, 2007 at 3:19 pm
How would I format the date of a report that is currently giving something like....
2007-08-02 09:00:00.000
(note: the date is already in a field...I'm not trying to get or manipulate anything from the current date)
I would like it to just produce or show in the format of...
mm/dd/yyyy
Do I use DATEPART? If so, I'm not sure what the syntax would be. Books Online doesn't give me anything solid (good examples) to go off of.
October 25, 2007 at 3:27 pm
If you do not need to order by the date, you can use CONVERT(CHAR(10),YourDate,101).
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 25, 2007 at 3:31 pm
If you do not need to order by the date, you can use CONVERT(CHAR(10),YourDate,101).
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 26, 2007 at 4:35 am
You can still order by that as well..
Or are you talking about something else, Greg?
J M - the place in BOL you're looking for is under 'CONVERT'
There you'll find the different displaystyles.
/Kenneth
October 26, 2007 at 6:45 am
Kenneth Wilhelmsson (10/26/2007)
You can still order by that as well..Or are you talking about something else, Greg?
J M - the place in BOL you're looking for is under 'CONVERT'
There you'll find the different displaystyles.
/Kenneth
No that is what I was talking about. I have just noticed sometimes there are differences in the sort order between a date in date format, and a date in char format. Seems like an oportunity for a good test.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 26, 2007 at 6:53 am
Well, I can't replicate it, but I know I had an issue with it before. Maybe I'm just crazy.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 26, 2007 at 6:53 am
Nah, probably just cuz it's Friday 😀
October 26, 2007 at 7:36 am
well - bad results or no bad results, you do want to avoid functions in your ORDER by. That's one where performance will SUCK.
Sort by the real date, display whatever they want.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 26, 2007 at 8:25 am
Greg Snidow (10/26/2007)
Well, I can't replicate it, but I know I had an issue with it before. Maybe I'm just crazy.
No, not crazy... both of the following will order data, incorrectly...
SELECT CONVERT(CHAR(10),YourDate,101)
FROM YourTable
ORDER BY CONVERT(CHAR(10),YourDate,101)
SELECT CONVERT(CHAR(10),YourDate,101) AS SomeDate
FROM YourTable
ORDER BY SomeDate...
... correct way to do it, of course, is...
SELECT CONVERT(CHAR(10),YourDate,101) AS SomeDate
FROM YourTable
ORDER BY YourDate
--Jeff Moden
October 26, 2007 at 8:34 am
Matt, do you have any example that shows such abysmal difference?
Tried a little, and sure, there is a piece of overhead between
ORDER BY datecolumn
and
ORDER BY CONVERT(CHAR(8), datecolumn, 112)
for example..
But far from 'sucks'...
Surely it's not *that* bad, is it?
Examples I tried:
-- scan 3.7 million row table
Select col1, datecol from tbl ORDER BY datecol
Select col1, datecol from tbl ORDER BY CONVERT(CHAR(8), datecol, 112)
The first timed in on 27 seconds, the 2nd on 32...
The plan for the 2nd is a bit bigger, there is an additional operator.
Also the estimated datasize gets larger, in this case 93MB for the first, 121MB for the second, so the convert is clearly adding overhead, though not by that much...
Also did the same queries by adding a criteria that would use a ci seek.
The plans still show the same differences, though when such few rows are involved, the percieved performance seems neglible.
/Kenneth
/Kenneth
October 26, 2007 at 8:41 am
Jeff Moden (10/26/2007)
Greg Snidow (10/26/2007)
Well, I can't replicate it, but I know I had an issue with it before. Maybe I'm just crazy.No, not crazy... both of the following will order data, incorrectly...
SELECT CONVERT(CHAR(10),YourDate,101)
FROM YourTable
ORDER BY CONVERT(CHAR(10),YourDate,101)
SELECT CONVERT(CHAR(10),YourDate,101) AS SomeDate
FROM YourTable
ORDER BY SomeDate...
Actually, there's nothing incorrect about any of these.
SQL Server is very obedient and sorts exactly as asked to..
...if that wasn't exactly what one intended, is another matter..
The 'fault' here lies with the one placing the question, not the answerer. 😉
/Kenneth
October 26, 2007 at 9:22 am
Kenneth -
when you take out the time to actually return the results - the performance is disgusting. Or rather - the performance difference is astounding.
Of course - on small datasets a "big performance difference" doesn't show up much.
Try this on. I'm removing the "display" aspect by simply forcing it to process but not return anything to the screen.
Here's the test data (the noisefield is to "simulate" all of the extra fields you would usually find in a prod table):
create table testpivot (rid int identity(1,1) not null, coID int not null,prodID int not null, orderamount money not null, dte datetime,noisefield char(100))
alter table testpivot
add primary key (rid) with fillfactor=100
--create clustered index pivot_pk on #testpivot(coid,prodid,rid)
go
insert #testpivot(coid,prodid,orderamount)
select top 5000000
cast(rand(cast(newid() as varbinary)) *20 as integer)+1,
cast(rand(cast(newid() as varbinary)) *50 as integer)+1,
cast(rand(cast(newid() as varbinary)) *35000 as money)+1,
dateadd(dd,cast(rand(cast(newid() as varbinary)) *50 as integer),0),
cast(newid() as char(100))
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
union all
select top 5000000
cast(rand(cast(newid() as varbinary)) *20 as integer)+1,
cast(rand(cast(newid() as varbinary)) *50 as integer)+1,
cast(rand(cast(newid() as varbinary)) *35000 as money)+1,
dateadd(dd,cast(rand(cast(newid() as varbinary)) *50 as integer),0),
cast(newid() as char(100))
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
go
Test script
drop index testpivot.ixtest
go
declare @g datetime
declare @t datetime
select @g=getdate()
select @t=dte from testpivot order by dte
select datediff(ms,@g,getdate()),'raw sort'
go
dbcc freeproccache
go
declare @g datetime
declare @t datetime
select @g=getdate()
select @t=dte from testpivot order by CONVERT(CHAR(8), dte, 112)
select datediff(ms,@g,getdate()),'convert sort'
go
create index ixtest on testpivot(dte)
select 'and now with an index'
dbcc freeproccache
go
declare @g datetime
declare @t datetime
select @g=getdate()
select @t=dte from testpivot order by dte
select datediff(ms,@g,getdate()),'raw sort'
go
dbcc freeproccache
go
declare @g datetime
declare @t datetime
select @g=getdate()
select @t=dte from testpivot order by CONVERT(CHAR(8), dte, 112)
select datediff(ms,@g,getdate()),'convert sort'
Results:
raw sort 52956
convert sort 89013
and now with an index
raw sort 2550
convert sort 72000
As I mentioned - on something small - this gets "covered over". But it's rather drastic.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 26, 2007 at 7:39 pm
Kenneth Wilhelmsson (10/26/2007)
Actually, there's nothing incorrect about any of these.SQL Server is very obedient and sorts exactly as asked to..
...if that wasn't exactly what one intended, is another matter..
The 'fault' here lies with the one placing the question, not the answerer. 😉
/Kenneth
As you know, the first two sort the dates in an incorrect date order...
Why the semantics, Ken?
--Jeff Moden
October 29, 2007 at 1:54 am
Because semantics *is* important, and this is a good example of why.
SQL is language where we construct questions, and as you ask, thou shalt be answered.
So, here we ask for something, and we get a perfectly correct answer according to what, and not to forget - how - we asked it. The server can't read minds, it has no notion of our intentions, so it's pretty important that we phrase our queries so that they will answer what we *intend* them to answer.
Here we ask for a result that should be ordered by a string value, and we get just that.
And we get an answer that is perfectly correctly sorted that way. :Wow:
Now, if this wasn't our *intention*, then who's to 'blame'?
Dang stupid box that can't tell what I *really* mean?
/Kenneth
October 29, 2007 at 2:29 am
Man, you talk a lot about nothing...
First two sorts are incorrect because they did not sort in the desired order.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply