Problem with date joins - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Problem with date joins

Author

Topic

scullee
Posting Yak Master

103 Posts

Posted - 2003-03-02 : 01:28:13

I have 2 tables, one that had a date value and one that has a date time value and i need to join the 2 together. I have tried doing a convert as part of the join but sql server doesnt seem to like it.

Does anyone have any idea how to do this?

The field FDFImportParsed.FileDate is the plain date without the time and TransferSendHeader.[Date] has the time on it that i want to ignore.

This is my attempt at getting it working but im having issues and SQL server is saying

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '.'.

 

SELECT FDFImportParsed.ID, TransferSendHeader.ID AS TransferSendHeaderID, FDFImportParsed.FileDate
FROM FDFImportParsed 
LEFT OUTER JOIN TransferSendHeader ON 
  CONVERT(varchar, FDFImportParsed.FileDate, 103) = CONVERT(TransferSendHeader.[Date], 103) AND 
  FDFImportParsed.Field11 = TransferSendHeader.TransferNumber
WHERE     (FDFImportParsed.Field1 = '11')

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-02 : 02:10:26

I dont think its anything major...just missing the datatype you want to convert TransferSendHeader.Date to:

SELECT FDFImportParsed.ID, TransferSendHeader.ID AS TransferSendHeaderID, FDFImportParsed.FileDate
FROM FDFImportParsed
LEFT OUTER JOIN TransferSendHeader ON
CONVERT(varchar, FDFImportParsed.FileDate, 103) = CONVERT(varchar, TransferSendHeader.[Date], 103) AND
FDFImportParsed.Field11 = TransferSendHeader.TransferNumber
WHERE (FDFImportParsed.Field1 = '11')

OS

scullee
Posting Yak Master

103 Posts

Posted - 2003-03-02 : 04:59:25

Oh, i am a moron.

I suppose thats what i get for rushing the code before i duck off to the football.

Thanks for the help :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources