February 24, 2004 at 3:41 pm
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?
February 24, 2004 at 3:54 pm
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
February 24, 2004 at 4:02 pm
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.
February 24, 2004 at 7:43 pm
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.
February 25, 2004 at 5:00 am
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:
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:
-Jeff Moden
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2004 at 6:17 am
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:
Then your comparison is
February 25, 2004 at 6:44 am
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
February 25, 2004 at 7:13 am
-- 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
February 25, 2004 at 7:54 am
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]
February 25, 2004 at 8:18 am
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
February 25, 2004 at 8:23 am
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]
February 25, 2004 at 8:46 am
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
February 25, 2004 at 9:01 am
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]
February 25, 2004 at 9:20 am
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
February 25, 2004 at 12:53 pm
Please explain...
"DO NOT Cast datetime to any type of int !!! "
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply