Geek asks and answers

How to join a date table in T-SQL?

In the past, I did RIGHT JOINS without any problems. However, for some reason, I cannot successfully join the date table in the date field. So, I have two tables. The first table has one date column and several more columns that do not contain dates. Then I have a date table that has only two date columns.

I initialize this date table by inserting the first monthly dates into it for 13 consecutive months. But my other table has only 9 months of data.

So table A looks like this:

col_A    col_B     col_C
-----    ------    -------
sfds     jkjlj     7-1-2009
rewr     sfsfsd    5-1-2009
xcxvg    sdfsfk    4-1-2009
...

But table B looks like this:

StartDate   EndDate
---------   ---------
7-1-2009    7-31-2009
6-1-2009    6-30-2009
5-1-2009    5-31-2009
...

But when I correctly attach table B to A like this:

    SELECT *
      FROM TABLE_A A
RIGHT JOIN TABLE_B B ON A.COL_C = B.StartDate

12 , Table_B 13 /. 9 . - , ? ?

- Table_A 13 , . 9 , 4 .

+3
3

, ( !).

DATEPART() CONVERT() ( ) , , , .

:

DATEADD(day, DATEDIFF(day, 0, DateColumn), 0)

.

SELECT *      
FROM TABLE_A A
RIGHT JOIN TABLE_B B ON 
   DATEADD(day, DATEDIFF(day, 0, A.COL_C), 0) = DATEADD(day, DATEDIFF(day, 0, B.StartDate), 0)

:

CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

.

+8

, col_A, col_B col_C , . , , where, ?

SELECT *
FROM TABLE_A A

sfds    jkjlj   2009-07-01 00:00:00.000


SELECT *
FROM TABLE_B B

2009-07-01 00:00:00.000 2009-07-31 00:00:00.000
2009-06-01 00:00:00.000 2009-06-30 00:00:00.000
2009-05-01 00:00:00.000 2009-05-31 00:00:00.000


SELECT *
FROM TABLE_A A
RIGHT JOIN TABLE_B B ON A.COL_C = B.StartDate

sfds    jkjlj   2009-07-01 00:00:00.000 2009-07-01 00:00:00.000 2009-07-31 00:00:00.000
NULL    NULL    NULL    2009-06-01 00:00:00.000 2009-06-30 00:00:00.000
NULL    NULL    NULL    2009-05-01 00:00:00.000 2009-05-31 00:00:00.000
+2

What I usually do when joining dates is to convert them to integers first, since joining dates (1) often gives unexpected results and (2) is slow.

I would make the connection as follows:

Select * 
From
TABLE_A A 
Right Join 
TABLE_B B 
On 
((Year(A.COL_C)*10000) + (Month(A.COL_C)*100) + Day(A.COL_C)) = ((Year(B.StartDate)*10000) + (Month(B.StartDate)*100) + Day(B.StartDate))

However, it looks like you really want to join the year and month, since the date on Table_A is always the first. I would do it like this:

Select * 
From
TABLE_A A 
Right Join 
TABLE_B B 
On 
((Year(A.COL_C)*100) + Month(A.COL_C)) = ((Year(B.StartDate)*100) + Month(B.StartDate))

Hope this helps.

0
source

More articles:


All Articles