Removing the time from a datetime database field

Question

Post reply

Removing the time from a datetime database field

Viewing 8 posts - 16 through 22 (of 22 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

noeld

SSC Guru

Points: 96590

February 25, 2004 at 1:03 pm

#495657

hamishahern ,

If you cast directly to int depending on the time portion of the datetime value you may get different DATES but , if you really need to do it, you can use the workaround that Frank posted above!

* Noel

srankin Say Hey Kid Points: 673 More actions · Answer 2

Simple.

select CAST(FLOOR(CAST(GETDATE() as float)) as datetime)

This:

1: Converts a datetime to a floating point number

2: Truncates the decimal postion (time) of the floating point number without changing the value of the integer portion

3: Converts the floating point number back into a datetime

This can be used anywhere you'd like.  No need to create a UDF or anything of the sort.  There may be faster ways (cpu cycles) of doing it but the total time you'd save you've wasted on this discussion thread.

linto SSC Eights! Points: 851 More actions · Answer 3

linto

SSC Eights!

Points: 851

February 25, 2004 at 3:12 pm

#495698

use DATEPART function and retrieve the date parts from the date retrieved.

declare @dtTime datetime

set @dtTime = getdate()

SELECT CAST(DATEPART(m, @dtTime)as varchar) +'/'+ CAST(DATEPART(d, @dtTime)as varchar) +'/'+ CAST(DATEPART(yy, @dtTime)as varchar)

Linto

Phil Parkin SSC Guru Points: 245813 More actions · Answer 4

Noeld – please explain why you should not cast a datetime as int. I have used

cast(cast(GETDATE() - 0.5 as integer) as DateTime)

with apparent success. Just interested ... the FLOOR solution already mentioned seems more elegant anyway.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

As I alluded to in my original posting...

Casting (or Converting) the date as INT or BIGINT may give you the wrong (next day) date because dates with times of 12:00 or later will be rounded up to the next day automatically. Don't use any form of INT.

Further, any type of conversion to DECIMAL will cause similar errors due to the automatic rounding inherent in the datatype.

The idea of a UDF (User Defined Function) is a great one.

--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)
Intro to Tally Tables and Functions

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Antares686

SSC Guru

Points: 125444

February 26, 2004 at 6:21 am

#495802

Could be the cache had enough data to perform without reads and the cost could go down. I suggest trying multiple times and see what happens. However thare could be other factors, would have to see the execution plan.