|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
JOIN based on date
How do you get rid of the time so that the JOIN statement below will work.
Table1.date 1/1/2008 22:00:00 Table2.date 1/1/2008 00:00:00 SQL statement: SELECT tb1.name, tb2.salary, tb1.date FROM Table1 tb1 INNER JOIN Table2 tb2 ON table2.date = table1.date On versions prior to SQL Server 2008 you can use the DATEADD and
DATEDIFF functions to trim the time portion. The general syntax is (here the date 1/1/2001 is just used as a base for the calculations, any other date will work): DATEADD(DAY, DATEDIFF(DAY, '20010101', date_column), '20010101') Essentially DATEDIFF counts boundaries crossed, and when you use it with days it eliminates the time portion. Then you add back the number of days to the same base date, resulting in your original date with time set at midnight. Your query can look like this (assuming the date column in Table2 has time set at midnight, otherwise you have to use the same expression on both sides): SELECT A.name, B.salary, A.date FROM Table1 AS A INNER JOIN Table2 AS B ON B.date = DATEADD(DAY, DATEDIFF(DAY, '20010101', A.date), '20010101'); You can also write the query this way: SELECT A.name, B.salary, A.date FROM Table1 AS A INNER JOIN Table2 AS B ON A.date >= B.date AND A.date < DATEADD(DAY, 1, B.date); It may be more efficient to define this expression removing the time portion as computed column that you can index. On SQL Server 2008 you can cast to the new data type DATE (or even better, if time is not needed at all change the data type of the columns to DATE). Thanks, Plamen. It works
Show quoteHide quote "Plamen Ratchev" wrote: > On versions prior to SQL Server 2008 you can use the DATEADD and > DATEDIFF functions to trim the time portion. The general syntax is (here > the date 1/1/2001 is just used as a base for the calculations, any other > date will work): > > DATEADD(DAY, DATEDIFF(DAY, '20010101', date_column), '20010101') > > Essentially DATEDIFF counts boundaries crossed, and when you use it with > days it eliminates the time portion. Then you add back the number of > days to the same base date, resulting in your original date with time > set at midnight. > > Your query can look like this (assuming the date column in Table2 has > time set at midnight, otherwise you have to use the same expression on > both sides): > > SELECT A.name, B.salary, A.date > FROM Table1 AS A > INNER JOIN Table2 AS B > ON B.date = DATEADD(DAY, > DATEDIFF(DAY, '20010101', A.date), '20010101'); > > You can also write the query this way: > > SELECT A.name, B.salary, A.date > FROM Table1 AS A > INNER JOIN Table2 AS B > ON A.date >= B.date > AND A.date < DATEADD(DAY, 1, B.date); > > It may be more efficient to define this expression removing the time > portion as computed column that you can index. > > On SQL Server 2008 you can cast to the new data type DATE (or even > better, if time is not needed at all change the data type of the columns > to DATE). > > -- > Plamen Ratchev > http://www.SQLStudio.com > On Dec 5, 11:02 am, morphius <morph***@discussions.microsoft.com>
wrote: > How do you get rid of the time so that the JOIN statement below will work.. If you frequently do things like this, then you need to persist the> Table1.date > 1/1/2008 22:00:00 > > Table2.date > 1/1/2008 00:00:00 > > SQL statement: > SELECT tb1.name, tb2.salary, tb1.date > FROM Table1 tb1 > INNER JOIN Table2 tb2 ON table2.date = table1.date truncated date value. In 2008, use DATE column, prior to 2008, use a computed column. An option that I typically see is
CONVERT (VARCHAR(10), table2.date, 101) = CONVERT (VARCHAR(10), table1.date, 101) Show quoteHide quote "morphius" <morph***@discussions.microsoft.com> wrote in message news:F5D4C434-5A00-4152-A8B8-EB950FC3984E@microsoft.com... > How do you get rid of the time so that the JOIN statement below will work. > Table1.date > 1/1/2008 22:00:00 > > Table2.date > 1/1/2008 00:00:00 > > SQL statement: > SELECT tb1.name, tb2.salary, tb1.date > FROM Table1 tb1 > INNER JOIN Table2 tb2 ON table2.date = table1.date > > > Michael, this throws a syntax error because the join is from two databases.
CONVERT (VARCHAR(10), database1.table2.date, 101) = CONVERT (VARCHAR(10),database2.table1.date, 101) Show quoteHide quote "Michael A." wrote: > An option that I typically see is > > CONVERT (VARCHAR(10), table2.date, 101) = CONVERT (VARCHAR(10), table1.date, > 101) > > > "morphius" <morph***@discussions.microsoft.com> wrote in message > news:F5D4C434-5A00-4152-A8B8-EB950FC3984E@microsoft.com... > > How do you get rid of the time so that the JOIN statement below will work. > > Table1.date > > 1/1/2008 22:00:00 > > > > Table2.date > > 1/1/2008 00:00:00 > > > > SQL statement: > > SELECT tb1.name, tb2.salary, tb1.date > > FROM Table1 tb1 > > INNER JOIN Table2 tb2 ON table2.date = table1.date > > > > > > > You forgot to put in your schema.
CONVERT (VARCHAR(10), database1.dbo.table2.date, 101) = CONVERT (VARCHAR(10),database2.dbo.table1.date, 101) Show quoteHide quote "morphius" <morph***@discussions.microsoft.com> wrote in message news:54E16D66-D63A-45D1-9E08-268E97FA9108@microsoft.com... > Michael, this throws a syntax error because the join is from two > databases. > > CONVERT (VARCHAR(10), database1.table2.date, 101) = CONVERT > (VARCHAR(10),database2.table1.date, 101) > > > "Michael A." wrote: > >> An option that I typically see is >> >> CONVERT (VARCHAR(10), table2.date, 101) = CONVERT (VARCHAR(10), >> table1.date, >> 101) >> >> >> "morphius" <morph***@discussions.microsoft.com> wrote in message >> news:F5D4C434-5A00-4152-A8B8-EB950FC3984E@microsoft.com... >> > How do you get rid of the time so that the JOIN statement below will >> > work. >> > Table1.date >> > 1/1/2008 22:00:00 >> > >> > Table2.date >> > 1/1/2008 00:00:00 >> > >> > SQL statement: >> > SELECT tb1.name, tb2.salary, tb1.date >> > FROM Table1 tb1 >> > INNER JOIN Table2 tb2 ON table2.date = table1.date >> > >> > >> > >> And T-SQL questions like this will get answered alot quicker if you post in
microsoft.public.sqlserver.programming The syntax i sent you from the last query assumes that your table is in the DBO schema. In most environments that is true but if it is not then just substitute the correct schema in place of DBO. CONVERT (VARCHAR(10), database1..table2.date, 101) = CONVERT (VARCHAR(10),database2..table1.date, 101) Show quoteHide quote "morphius" <morph***@discussions.microsoft.com> wrote in message news:54E16D66-D63A-45D1-9E08-268E97FA9108@microsoft.com... > Michael, this throws a syntax error because the join is from two > databases. > > CONVERT (VARCHAR(10), database1.table2.date, 101) = CONVERT > (VARCHAR(10),database2.table1.date, 101) > > > "Michael A." wrote: > >> An option that I typically see is >> >> CONVERT (VARCHAR(10), table2.date, 101) = CONVERT (VARCHAR(10), >> table1.date, >> 101) >> >> >> "morphius" <morph***@discussions.microsoft.com> wrote in message >> news:F5D4C434-5A00-4152-A8B8-EB950FC3984E@microsoft.com... >> > How do you get rid of the time so that the JOIN statement below will >> > work. >> > Table1.date >> > 1/1/2008 22:00:00 >> > >> > Table2.date >> > 1/1/2008 00:00:00 >> > >> > SQL statement: >> > SELECT tb1.name, tb2.salary, tb1.date >> > FROM Table1 tb1 >> > INNER JOIN Table2 tb2 ON table2.date = table1.date >> > >> > >> > >>  
Other interesting topics
Disaster Recovery Options
What can cause SQL 2000 to stop responding for a while replicate image of DB at a remote site Is it possible to associate sql login with network address? Using Alias with Failover Problem in sysdatabases SQL Server 2005 Script Out User Permission Prod-> Dev FT Index - Avoid Noise Words how to ignore error.... Database sometimes not accesable in SQL Server 2005 Express |
|||||||||||||||||||||||
