Removing the time from a datetime database field

Question

Removing the time from a datetime database field

Jason Lieber

SSC Rookie

Points: 39
More actions
February 24, 2004 at 3:41 pm

#82327

I have records with and without the time in a daily extract I receive I need to join on this date field but the join doesn't work between two tables because I have a time in one table and not in the other. I need to scrub it daily in my import DTS...any suggestions?

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

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

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 1

Take a look at the Cast and Convert page in Books Online (BOL). There are styles you can use. Perhaps you can convert to a char field and do the comparison. Or you use datepart and join on the three different date parts mm, dd, yy

Michelle

Jason Lieber SSC Rookie Points: 39 More actions · Answer 2

Do you have the style syntax, I tried 102 as a style but could not get it to parse. Joining on three parts would take a lot of overhead. Thanks for your suggestions.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

The one without time is midnight. To do the same on the other join like so

ON

tbl1.dtfield = dateadd(d,datediff(d,0,tbl2.dtfield),0)

this assumes tbl2 is the one with time. It strips to midnight and they will be equal.

Jeff Moden SSC Guru Points: 1003948 More actions · Answer 4

Jason,

Just in case you still don't have an answer...

If you want to do the join as if TIME where not included in the column and without changing the underlying data (I always try to preserve original data), try using this example as a WHERE join... you can always convert it to an INNER join, if you'd like:

SELECT ya-da,ya-da,ya-da
FROM TableA a, TableB b
WHERE FLOOR(CONVERT(FLOAT,a.datefield))=
FLOOR(CONVERT(FLOAT,b.datefield))

The underlying math for dates is that they're really a decimal like number where everything to the left of the decimal point is the number of DAYS since 01/01/1900. Everything to the right of the decimal point is parts of a day or TIME. The conversion to FLOAT converts the datefields to that decimal-like number. FLOOR truncates the decimal portion so you have a whole number (days only, no time). You don't want to use INT in place of the FLOOR and FLOAT combination because INT may round up based on the decimal portion of the underlying Date-Serial.

If you don't care about the original data and you want to update the field so it no longer has TIME in it but will still be treated like a date, you'll need to do an UPDATE to the table with a double conversion. Like this:

UPDATE TableA
SET datefield=CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,datefield)))

-Jeff Moden

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

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 5

Or... If you do this on a regular basis and don't want to keep having to remember the FLOOR(CONVERT(FLOAT,var)) syntax you can write a UDF:

CREATE FUNCTION dbo.DATEONLY (@dt datetime)
RETURNS DECIMAL
AS
BEGIN
RETURN (FLOOR(CONVERT(FLOAT,dt)))
END
GO

Then your comparison is

WHERE dbo.DATEONLY(a.datefield) = dbo.DATEONLY(b.datefield)

John Campbell Valued Member Points: 57 More actions · Answer 6

Although not as fast as the (FLOOR(CONVERT(FLOAT,dt))) style of doing things, you could also compare the 2 dates as a character by using the style descriptor in the convert function. This makes for a little easier reading of the function.

select convert(char(10),getdate(),102)

-- 2004.02.25

TONYMARKS SSC Journeyman Points: 77 More actions · Answer 7

-- As one other forum member said, the time in a datetime column is stored --- as a decimal. The trick in the code below simply truncates the decimal

--

-- Example using no work variable: replace "getdate()"

-- with your datetime column name.

--

select cast(cast(getdate() as bigint) as datetime)

-- or ...

--

-- Example using a work variable: replace "getdate()"

-- with your datetime column name

--

declare @x datetime

-- Simulates a column that contains a date and time

set @x=getdate()

-- The time is held internally as a fraction: truncate it!

set @x=cast(cast(getdate() as bigint) as datetime)

-- The time is a fraction: truncate it!

select @x

TONYMARKS

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

More than one way to skin that cat.

for the sake of simplicity, I would use Antares686 solution. I bet it is also the one with best performance. At least compared to those which use a UDF.

But...

select cast(cast(getdate() as bigint) as datetime)

select getdate()

select dateadd(d,datediff(d,0,getdate()),0)

select cast(cast(getdate() as bigint) as datetime)

------------------------------------------------------

2004-02-25 15:46:58.990

(1 row(s) affected)

------------------------------------------------------

2004-02-25 00:00:00.000

(1 row(s) affected)

------------------------------------------------------

2004-02-26 00:00:00.000

(1 row(s) affected)

???

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

noeld SSC Guru Points: 96590 More actions · Answer 9

DO NOT Cast datetime to any type of int !!!

Better

Left(<datetime>,11)

or

SUBSTRING(CAST(<datetime> AS binary(8)),1,4)

or

.... Like above

* Noel

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

Actually you can cast to int, but then you have to use something like

select cast(cast(getdate()-.5 as bigint) as datetime)

select getdate()

select dateadd(d,datediff(d,0,getdate()),0)

select cast(cast(getdate()-.5 as bigint) as datetime)

------------------------------------------------------

2004-02-25 16:17:36.530

(1 row(s) affected)

------------------------------------------------------

2004-02-25 00:00:00.000

(1 row(s) affected)

------------------------------------------------------

2004-02-25 00:00:00.000

(1 row(s) affected)

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

noeld SSC Guru Points: 96590 More actions · Answer 11

I never meant that with more arithmetic could be done I just meant straight to int .That is why is a good thing to know about the actual storage format that is used by the engine!

* Noel

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

Well, I wouldn't consider a simple subtraction 'more arithmetic'

But, I agree that it never hurts to look behind the scenes. Fortunately those problems will vanish with Yukon.

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

kkeeffe SSC Eights! Points: 989 More actions · Answer 13

I tried the following using two tables each of which have a non-clustered index on the date field. I was suprised to see that the estimated cost for the first query (using Antares686's solution) was 1246, while the cost for the second query was only 328.

Any idea why that would be?

--Query 1:

Select t1.CustomerId

From Table1 t1

Inner Join Table2 t2

On t1.Date1 = Dateadd(dd, Datediff(dd, 0, t2.Date2),0)

-- Query 2:

Select t1.CustomerId

From Table1 t1

Inner Join Table2 t2

On Year(t1.Date1) = Year(t2.Date2)

And Month(t1.Date1) = Month(t2.Date2)

And Day(t1.Date1) = Day(t2.Date2)

thanks

k2

hamishahern SSC-Addicted Points: 498 More actions · Answer 14

Please explain...

"DO NOT Cast datetime to any type of int !!! "