Removing Time from SmallDateTime

Question

Post reply

Removing Time from SmallDateTime

SueStill

SSC Eights!

Points: 946
More actions
February 25, 2004 at 12:04 pm

#84296

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???

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

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.

noeld SSC Guru Points: 96590 More actions · Answer 2

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

srankin Say Hey Kid Points: 703 More actions · Answer 3

SELECT CAST(FLOOR(CAST(GETDATE() as float)) AS smalldatetime)

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 4

I have tried both methods as below. The DateDiff method turns out 5.5 percent faster on my loval installation of SS7 :

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Yes, it should almost always be faster!

But I am having a deja vu right now

http://www.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]

RameshR SSC Enthusiast Points: 117 More actions · Answer 6

Why dont u try this:

SELECT CONVERT(Varchar(10),GetDate(),120)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

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]

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 8

CAST(CAST(GETDATE() AS Char(11)) AS DateTime) took 4 times as long as datediff or numeric on my server!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

q.e.d.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Tom Hamilton-126456 Mr or Mrs. 500 Points: 527 More actions · Answer 10

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

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]

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 12

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 13

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]

Antares686 SSC Guru Points: 125444 More actions · Answer 14

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.