Formatting the date

Question

Formatting the date

J M-314995

SSCrazy

Points: 2975
More actions
October 25, 2007 at 3:19 pm

#70791

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.

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Greg Snidow SSCoach Points: 16108 More actions · Answer 1

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.

Greg Snidow SSCoach Points: 16108 More actions · Answer 2

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.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

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

Greg Snidow SSCoach Points: 16108 More actions · Answer 4

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.

Greg Snidow SSCoach Points: 16108 More actions · Answer 5

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.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Kenneth Wilhelmsson

SSC-Dedicated

Points: 30043

October 26, 2007 at 6:53 am

#745679

Nah, probably just cuz it's Friday 😀

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

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?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

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 Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 10

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

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?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 13

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

Man, you talk a lot about nothing...

First two sorts are incorrect because they did not sort in the desired order.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)