Removing Time from SmallDateTime
-
Is there any way to actually store just the date in SQL as a date data type? I have been forced to use char(8) or char(10) to do this in the past - it's part of my primary key and including the time would be the difference between 1m records and 100m records over a month's time. The problem with char is that when I do reporting, I have to always remember to convert and to tell ad-hoc reporters to convert. I also miss things like the calendar function in Crystal that recognizes when a date-type field is used and automatically calls the comctl system file for a calendar control. Sometimes, I also want the time, but just want to dim the parameter as date only. When dimming as smalldatetime, Crystal asks the user for both the date and the time - while I need it for the app, they do not need to see or select it for reporting. Any ideas???
-
No but you could force the time to midnight for all by doing
DATEADD(d,DATEDIFF(d,0,[value/column/getdate()]),0)
So doing
DATEADD(d,DATEDIFF(d,0,getdate()),0)
will give you current day but time will be midnight.
-
My Approach to that problem is to use 2 separated fields one with date info and another with time info when inserting / updating data I stripp the unneeded part off (You could use CALULATED FIELDS for this purposes
). Then on your reports use the Field That you need!I can't wait for yukon DATE and TIME Types
* Noel -
SELECT CAST(FLOOR(CAST(GETDATE() as float)) AS smalldatetime)
-
I have tried both methods as below. The DateDiff method turns out 5.5 percent faster on my loval installation of SS7 :
-
Yes, it should almost always be faster!
But I am having a deja vu right now
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=102103
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Why dont u try this:
SELECT CONVERT(Varchar(10),GetDate(),120)
-
set nocount on
set statistics time on
select dateadd(d,datediff(d,0,msgdatereceived),0) from mails_header
SELECT CAST(FLOOR(CAST(msgdatereceived as float)) AS smalldatetime) from mails_header
select convert(varchar(10),msgdatereceived,120) from mails_header
set statistics time off
set nocount off
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 441 ms.
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 321 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 533 ms.
It's the slowest of the given alternatives.
And I did this only on +50k rows
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
CAST(CAST(GETDATE() AS Char(11)) AS DateTime) took 4 times as long as datediff or numeric on my server!
-
q.e.d.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I use a function to wrap a convert process. This will yield the date part, delimited with forward slashes. If you prefer a period alter the 101 to a 102, or as noted ealier, for hyphens change the 101 to 120. These numbers are referred to as Styles.
Here's the Function called fn_DateOnly
CREATE FUNCTION dbo.fn_DateOnly (@DateIn datetime)
RETURNS varchar(10)
AS
BEGIN
RETURN convert(char(10),@DateIn,101)
END
By the way a variation of which will help control the decimals on money data where you can use:
CREATE FUNCTION dbo.fn_Money2 (@MoneyIn Money)
RETURNS varchar(10)
AS
BEGIN
RETURN Convert(varchar, @MoneyIn, 1)
END
- HTH Tom
-
There is no need for a function. In fact, in this case it will even more slow down things as SQL Server processes functions rows by row AFAIK
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
For a SELECT, BETWEEN seems the most efficient :
SET DATEFORMAT dmy
DECLARE @DayStart DateTime
DECLARE @DayEnd DateTime
SET @DayStart = CAST('2/2/2004' AS DateTime)
SET @DayEnd = CAST('3/2/2004' AS DateTime)
SELECT * FROM MyTable WHERE CreatedDate BETWEEN @DayStart AND @DayEnd
Returning 200,000 rows from a 44m row table on an unindexed field, this was 5% faster than
SELECT * FROM MyTable WHERE DATEADD(d, DATEDIFF(d, 0, CreatedDate), 0) = @DayStart
-
Okay, but in most cases you run into problems when trying to JOIN on datetimes that still have the time portion.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I have found that you need to make sure the buffers are cleared or run mutiple times to see which is truely faster. Consider which you ran first and the fact it may not have had to read from the drive for the other, also consider if you had anything else going on. Heck I have found doing some things one way on some tables work fine but on other tables wasn't as great. Test, Test, Test.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply