Сортировки и форматирования в запросе никаких не делаю.
Дату беру так: Tbl_Projects.Project_Date as 'Tbl_Projects.Project_Date'
форматирование даты сейчас закомментировано.
Полный текст запроса приведен ниже.
Он большой, т.к. рассчитан еще на фильтрацию по значениям ряда полей на форме
.
.
.
-- Запрос по кнопке Btn_Do_Project_Search
SELECT DISTINCT
CAST(Tbl_Projects.id AS Unsigned),
-- DATE_FORMAT(Tbl_Projects.Project_Date,'%Y-%m-%d') as 'Tbl_Projects.Project_Date' /* Дата проекта из строки преобразована в дату
Tbl_Projects.Project_Date as 'Tbl_Projects.Project_Date' ,
Tbl_Projects.Project_Num as 'Tbl_Projects.Project_Num' /*Номер КП*/ ,
Tbl_Projects.Project_Sum as 'Tbl_Projects.Project_Sum' /*Сумма КП*/ ,
Tbl_Clients.Client_Nick as 'Tbl_Clients.Client_Nick' /*Имя заказчика*/ ,
Tbl_Projects.RFQ_From as 'Tbl_Projects.RFQ_From' /*Запрос от */ ,
Tbl_Projects.RFQ_email as 'Tbl_Projects.RFQ_email' /*email - запроса*/ ,
Tbl_Projects.Project_Nick as 'Tbl_Projects.Project_Nick' /*Имя проекта (Шифр)*/ ,
Tbl_Projects.Last_Project_Message as 'Tbl_Projects.Last_Project_Message' /*Комментарий к проекту*/ ,
Tbl_Project_Statuses.Project_Status_Name as 'Tbl_Project_Statuses.Project_Status_Name' /*Статус проекта, например, Готовим КП*/ ,
CASE WHEN Tbl_Projects.IsClosed = 0 THEN 'ОТКРЫТ' ELSE '-' END as 'Tbl_Projects.IsClosed' /*Состояние проекта - Открыт/Закрыт*/ ,
Tbl_Projects.id_Tbl_Clients as 'Tbl_Projects.id_Tbl_Clients' /*ID заказчика в таблице Проектов*/ ,
Tbl_Projects.id_Tbl_Project_Statuses as 'Tbl_Projects.id_Tbl_Project_Statuses' /*ID статуса в таблице проектов. Не удалять, т.к. отбор по статусу не будет работать*/ ,
Tbl_Colors.Color_HEX_Code as 'Tbl_Colors.Color_HEX_Code' /* Цветовая метка */,
Tbl_Colors.Color_Name as 'Tbl_Colors.Color_Name' /* Значение метки */,
Tbl_Projects.id /* id должен быть в конце, чтобы работало штатное редактирование/удаление записи*/
FROM Tbl_Projects
LEFT OUTER JOIN Tbl_Clients ON Tbl_Projects.id_Tbl_Clients=Tbl_Clients.id
LEFT OUTER JOIN Tbl_Project_Statuses ON Tbl_Projects.id_Tbl_Project_Statuses=Tbl_Project_Statuses.id
LEFT OUTER JOIN Tbl_Colors ON Tbl_Projects.id_Tbl_Colors=Tbl_Colors.id
WHERE
(CASE WHEN ({DateTimePicker_Period_From} IS NULL) THEN 1=1 ELSE ( Date(Tbl_Projects.Project_Date) >= Date({DateTimePicker_Period_From})) END) AND
(CASE WHEN ({DateTimePicker_Period_To} IS NULL) THEN 1=1 ELSE ( Date(Tbl_Projects.Project_Date) <= Date({DateTimePicker_Period_To})) END) AND
(CASE WHEN ('{Edit_Search_Project_ID}' = '') THEN 1=1 ELSE ( Tbl_Projects.id = '{Edit_Search_Project_ID}') END) AND
(CASE WHEN ('{Edit_Search_Project_Number}' = '') THEN 1=1 ELSE ( Tbl_Projects.Project_Num LIKE '%{Edit_Search_Project_Number}%') END) AND
(CASE WHEN ('{Edit_Search_Project_Client}' = '') THEN 1=1 ELSE ( (Tbl_Clients.Client_Nick LIKE '%{Edit_Search_Project_Client}%') OR
(Tbl_Projects.RFQ_From LIKE '%{Edit_Search_Project_Client}%') OR
(Tbl_Projects.RFQ_email LIKE '%{Edit_Search_Project_Client}%') ) END) AND
(CASE WHEN ('{Edit_Search_Project_Nik}' = '') THEN 1=1 ELSE ( (Tbl_Projects.Project_Nick LIKE '%{Edit_Search_Project_Nik}%') OR
(Tbl_Projects.Last_Project_Message LIKE '%{Edit_Search_Project_Nik}%') OR
(Tbl_Projects.RFQ_body LIKE '%{Edit_Search_Project_Client}%')) END) AND
-- (CASE WHEN ({ComboBox_Project_Status} < 0) THEN 1=1 ELSE ( Tbl_Projects.id_Tbl_Project_Statuses = '{Edit_StatusForSQL}' ) END) AND
(CASE WHEN ({ComboBox_Project_Status} < 0) THEN 1=1 ELSE ( Tbl_Projects.id_Tbl_Project_Statuses = '{ComboBox_Project_Status}' ) END) AND
(CASE WHEN ({CheckBox_IsClosed} = 0) THEN (Tbl_Projects.IsClosed = 0) ELSE ( (Tbl_Projects.IsClosed = 1) OR (Tbl_Projects.IsClosed = 0) ) END)