August 3, 2009 at 5:29 am
drew.allen (8/2/2009)
Jeff Moden (8/2/2009)
Does SSRS have something in it to take an SQL DateTime and format it for display without disturbing the sortability of the underlying SQL DateTime?If all else fails, you can always do the conversion in the dataset. You can create a VB expression in SSRS using the FormatDateTime() function to format a datetime string as a general date, long date, short date, long time, or short time. If you want a short date, you can also use CStr().
Drew
Unnecessary overkill. See my previous post.
August 3, 2009 at 6:41 am
try this:
convert(char(10),convert(datetime, 'jul 5 2009 12:00 AM'),101)
August 3, 2009 at 6:46 am
zubamark (8/3/2009)
try this:convert(char(10),convert(datetime, 'jul 5 2009 12:00 AM'),101)
Yep. That is the answer that was provided earlier in the thread.
August 3, 2009 at 3:07 pm
I agree that formmating in the front end is the way to go if possible as far as the question is concerned.
On a more general note, however, there are other cases where this is less practical. For example if one is sending an email from SQL server and you want to include the date but not the time in the message. For some applications in some columns it is only meaningful to store the date and not the time.
Sadly the various ways of getting the answer are moderately obtuse, and I can never remember them accurately, so I tend to wrap it up in a function and call it when I need it (albeit at a small performance overhead). For more suggestions and info, google 'sql server dateonly'.
CREATE FUNCTION [dbo].[DateOnly]
(
@DateTime DateTime
)
RETURNS DateTime
AS
BEGIN
RETURN DateAdd(dd, 0, Datediff(dd, 0, @dateTime))
END
Then
SELECT dbo.DateOnly(GetDate()) -- or whatever.
Regards
Mike Tanner (first posting and a SQL Server relative newbie, so please don't bite my head off!)
August 3, 2009 at 3:50 pm
Mike,
Welcome to SSC and thank you for sharing that function.
You should consider your head safe..... Your post made it clear that defining a function to do this costs something in performance, and the rest of this thread has several posts concerning when it's appropriate to format a datetime to begin with.
BTW, it's nice to know that the gearheads (an affectionate term for their engineers) at MS finally recognized how common it is in the business world to deal with dates and clock times as separate entities. SQL 2008 adds several new data types in this area, including DATE and TIME.
August 4, 2009 at 9:41 am
john.arnott (8/3/2009)
Mike,Welcome to SSC and thank you for sharing that function.
You should consider your head safe..... Your post made it clear that defining a function to do this costs something in performance, and the rest of this thread has several posts concerning when it's appropriate to format a datetime to begin with.
BTW, it's nice to know that the gearheads (an affectionate term for their engineers) at MS finally recognized how common it is in the business world to deal with dates and clock times as separate entities. SQL 2008 adds several new data types in this area, including DATE and TIME.
Heh... Although I can imagine some uses for those two datatypes, I just can't wait for their abuse to come about much like cursors were abused way back in version 6.5. I can just see questions coming up like "How do I combine the date and time from these two columns so I can actually do some date math with them". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2009 at 9:45 am
nigel (8/3/2009)
Jeff Moden (8/2/2009)
Does SSRS have something in it to take an SQL DateTime and format it for display without disturbing the sortability of the underlying SQL DateTime?Yes, it does.
You use the field's Format property, which uses standard .NET format strings. See here
That's what I thought. I just didn't know how it was done because I don't use SSRS but knew there had to be a way. Thanks for the link, Nigel. I've added the link to my tool box and I'm going to refer to it when this type of question comes up again like it has so very many times in the past.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2009 at 10:23 am
SQL (8/3/2009)
I agree that formmating in the front end is the way to go if possible as far as the question is concerned.On a more general note, however, there are other cases where this is less practical. For example if one is sending an email from SQL server and you want to include the date but not the time in the message. For some applications in some columns it is only meaningful to store the date and not the time.
Sadly the various ways of getting the answer are moderately obtuse, and I can never remember them accurately, so I tend to wrap it up in a function and call it when I need it (albeit at a small performance overhead). For more suggestions and info, google 'sql server dateonly'.
CREATE FUNCTION [dbo].[DateOnly]
(
@DateTime DateTime
)
RETURNS DateTime
AS
BEGIN
RETURN DateAdd(dd, 0, Datediff(dd, 0, @dateTime))
END
Then
SELECT dbo.DateOnly(GetDate()) -- or whatever.
Regards
Mike Tanner (first posting and a SQL Server relative newbie, so please don't bite my head off!)
Very cool and welcome aboard, Mike!
While your "head" is safe here, I would like to suggest that using a function for such a thing is not a "small performance overhead". It's rather large especially if you're working with huge batches like some of us do. Let's do a quick little experiment and see just how bad the use of such a function can affect performance. First, we need a lot of data to test against. To wit, here's the generic test table that I do a lot of testing against. It makes a million row test table with a whole lot of randomized data and takes about 36 seconds to build on my humble home machine...
--===== Conditionally drop the test table for reruns
IF OBJECT_ID('TempDB..#JBMTest','U') IS NOT NULL
DROP TABLE #JBMTest
GO
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 100,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
-- Jeff Moden
SELECT TOP 1000000
SomeID = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
SomeHex12 = RIGHT(NEWID(),12)
INTO #JBMTest
FROM Master.sys.All_Columns ac1,
Master.sys.All_Columns ac2 --Lack of join criteria makes this a CROSS-JOIN
;
--===== A table is not properly formed unless a Primary Key has been assigned
-- Takes about 1 second to execute.
ALTER TABLE #JBMTest
ADD PRIMARY KEY CLUSTERED (SomeID)
;
Now... let's test how fast just the coversions take place between the fine function you built and the straight code. The following code just does the conversion and throws it away in a "bit bucket" variable to keep the disk and the display I/O out of the picture...
DECLARE @Bitbucket DATETIME;
PRINT '========== Direct calculation ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = DATEADD(dd,DATEDIFF(dd,0,SomeDate),0)
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
PRINT '========== Original Function ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = dbo.DateOnly(SomeDate)
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
Here's what that produces on my box...
========== Direct calculation ==========
SQL Server Execution Times:
CPU time = 844 ms, elapsed time = 919 ms.
========== Original Function ==========
SQL Server Execution Times:
CPU time = 82015 ms, elapsed time = 149514 ms.
If we do the math on that, doing the calculation directly uses 96 times less CPU time than the function and the direct calculation is 161 times faster than the function in overall duration.
Another way to look at it, perhaps with even more impact, is that doing the conversion directly on a million rows took less than a second... using the function took almost a minute and a half.
My suggestion would be that although it's a bit obtuse, it's one of those things that are definitely worth memorizing. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply