join between two different formats

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

join between two different formats

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-05-24 : 02:01:12

hi all
i need to join two date fields from 2 different tables
table1 yearmonth(nvarchar(255),null) 2013-jan
table2 invoicedate(datetime,null) 2012-12-31 00:00:00.000
and table.yearmonth is my input parameter

i am trying possible ways .pls help on this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-24 : 02:11:19

so for yearmonth do you want it to be joined to every day of the year from the other table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-05-24 : 02:23:25

hi

yearmonth field need to join everymonth of second table

mohan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-24 : 02:54:18

sorry that doesnt make sense. You already have month coming in yearmonth field. What do you mean by
yearmonth field need to join everymonth of second table

how can record of 2013-Jan be linked to other months like Feb,Mar etc? whats the rule for that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-05-24 : 07:58:56

The preformance of this will be bad but you might try:

SELECT ...
FROM Table1 
INNER JOIN Table2 
    ON Table1.yearmonth = CAST(YEAR(Table2.invoicedate) AS VARCHAR(4)) + '-' + CONVERT(VARCHAR(3), Table2.invoicedate, 100)

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources