Conversion failed when converting date and/or time from character string 1

[dwg23]

Hello,
I am attempting to get the number of days between two dates.
I have used the datediff function to do this and keep getting the "Conversion failed when converting date and/or time from character string" Error.
Both fields are a datetime field and I have looked for bad data in all of the cells so I am at a loss as to what is wrong.

Idea's?

DATEDIFF(DAY, 'tblarInvoice.InvoiceDate','tblarCashReceipt.PaymentDate') As 'Total Days'

 

[Andrzejek]

Try dropping single quotes:

DATEDIFF(DAY, tblarInvoice.InvoiceDate, tblarCashReceipt.PaymentDate) As 'Total Days'

What are the values of tblarInvoice.InvoiceDate and tblarCashReceipt.PaymentDate ?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dwg23]

WOW! Talk about can't see the trees for the forest!
Dropping the single quotes worked.
The values were just normal date time of when the Invoice was printed and payment received.

Thanks!

 

[Olaf Doschke]

Most probably you didn't think single quotes make a literal string value out of the field names.

MSSQL Server has name delimiters and the name conventions are as follows:

DBNAME.Schema.TableName.ColumnName is a fully qualified name (with linked servers you even can prefix servername), typical schema is database owner (dbo) and the usuually sufficiently unique name is Tablename.Columnname, often enough even just the column name.

To allow spaces in names MSSQL offers name delimiters, so you can also write all parts of a name within square brackets like [dbo].[Table Name].[Column Name], and another delimiter for names is a double quote, so also "Table Name"."Column Name" would fit, but single quotes are string delimiters, not name delimiters.

In other databases (especially MySQL) name delimiters are backticks (`tablename`), that looks quote similar to single quotes. And in many other programming lagnuages both single and double quotes are string delimiters. Unfortunately there is no rule fitting any case, any database or programming language, you always have to switch your mindset to the current technology piece you use.

Bye, Olaf.