Syntax error converting datetime from character string 2

[SA812]

Hey guys i'm trying to use a DATEDIFF function by calling GETDATE within the DATEDIFF function. I get the error above when i try to insert into the SQL view. I know if i save the view and then create another view and reference the first view it will work. Is there a way to just use one view instead of two? I've copied my SQL view below:

SELECT dbo.tarCustomer.CustName, dbo.tarCustLastTran.TranDate, dbo.tarCustLastTran.TranType, dbo.tarCustLastTran.TranID, GETDATE() AS Date2,
DATEDIFF(m,TranDate,'DATE2')
FROM dbo.tarCustLastTran INNER JOIN
dbo.tarCustomer ON dbo.tarCustLastTran.CustKey = dbo.tarCustomer.CustKey
WHERE (dbo.tarCustLastTran.TranType = 501)

Thanks for your help.
SA

 

[gmmastros]

[tt][blue]
SELECT dbo.tarCustomer.CustName,
dbo.tarCustLastTran.TranDate,
dbo.tarCustLastTran.TranType,
dbo.tarCustLastTran.TranID,
GETDATE() AS [!]Date2[/!],
DATEDIFF(m,TranDate,[!]'DATE2'[/!])
FROM dbo.tarCustLastTran INNER JOIN
dbo.tarCustomer ON dbo.tarCustLastTran.CustKey = dbo.tarCustomer.CustKey
WHERE (dbo.tarCustLastTran.TranType = 501)
[/blue][/tt]

You cannot use an alias the way you are doing it. Try this...

SELECT dbo.tarCustomer.CustName,
       dbo.tarCustLastTran.TranDate,
       dbo.tarCustLastTran.TranType,
       dbo.tarCustLastTran.TranID, 
       GETDATE() AS [!]Date2[/!],
       DATEDIFF(m,TranDate,[!]GetDate()[/!])
FROM   dbo.tarCustLastTran INNER JOIN
       dbo.tarCustomer ON dbo.tarCustLastTran.CustKey = dbo.tarCustomer.CustKey
WHERE  (dbo.tarCustLastTran.TranType = 501)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

What is DATE2 in DateDiff?
You can't use a column from the SELECT because it not exists the recordset is fully prepared.

SELECT dbo.tarCustomer.CustName,
       dbo.tarCustLastTran.TranDate,
       dbo.tarCustLastTran.TranType,
       dbo.tarCustLastTran.TranID,
       GETDATE() AS Date2,
       DATEDIFF(m,TranDate,GETDATE()) AS DiffM
FROM dbo.tarCustLastTran
     INNER JOIN dbo.tarCustomer
           ON dbo.tarCustLastTran.CustKey =
              dbo.tarCustomer.CustKey
WHERE (dbo.tarCustLastTran.TranType = 501)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SA812]

Thanks guys that was perfect. It's always something simple. I'm still learing this whole SQL thing.