Issue with datetime format of attribute field

Submitted by feltron_cq on 2016-02-15

First of all, many thanks for the tool! It's excellent!

I'm having a little issue, which I hope you can help with.

We have version 1.1.4 installed using a MSSSQL server 2008 database.

I have an attrbute "closed date" which displays as dd-mm-yyyy hh:mm in the interface, which is correct according to our default date format.

However, when I sort this column for a view, it is causing an error when it finds a date which cannot be a US format date (such as 14-02-2016)

This is stored as attr_value = '2016-02-14' in the attr_values table

I've done some digging and our SQL server still has the default language setting, so 

SELECT CAST ('2016-02-14' as datetime) fails
whereas 
SELECT CAST ('2016-14-02' as datetime) results in 2016-02-14 00:00:00.000

I can get around this failing by sending 

set dateformat ymd
SELECT CAST ('2016-02-14' as datetime)

or alternatively 

SELECT convert(datetime, '2016-02-14',101)

Although it would make sense to change the local/language of our SQL server to British (as we are in Australia we use the same date format) I am reluctant to do this as there are a number of other applications using the database and I don't want to inadvertently break one of them.

Unfortunately your app is using the CAST method, and I cannot find where to hack this to send a dateformat request.

Would appreciate any assistance you can offer.

You can modify the castExpression() function in system/db/mssql/engine.inc.php to replace the cast() function with convert().

Regards,
Michał

Thanks for that. Definitely on the right track, but having changed the code to: 

public function castExpression( $expression, $type )
    {
        switch ( $type ) {
            case 'd':
                return "CAST( $expression AS int )";
            case 'f':
                return "CAST( $expression AS decimal(14,6) )";
            case 's':
                return "CAST( $expression AS nvarchar(max) )";
            case 't':
                return "CONVERT(datetime, $expression, 101 )";
            default:
                throw new System_Db_Exception( 'Invalid type' );
        }
    }

I'm seeing the following in SQL Profiler: 

ORDER BY CONVERT(datetime DESC, a15.attr_value DESC, 101 ) DESC

so it's throwing a couple of extra DESC keywords in. This appears when I change to the view where the date is sorted in descending order.

Hi there,

Sorry to re-open this, but we are now having another datetime issue and I felt it would be helpful to reference this previous discussion.

So we now wish to create a view such as Requested Date < [Today]

The SQL generated is 

exec sp_executesql N'SELECT COUNT(*) FROM issues AS i LEFT OUTER JOIN attr_values AS a8 ON a8.issue_id = i.issue_id AND a8.attr_id = @P1 WHERE i.folder_id = @P2 AND CONVERT(datetime,a8.attr_value,101) < @P3',N'@P1 int,@P2 int,@P3 nvarchar(16)',8,1,N'2016-03-16 14:00'

But this is throwing an error
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.