CODEWITHSUNDEEP
c#sql-serverasp.net-mvc
Rituraj Mishra
Rituraj Mishra

Reputation: 141

System.Data.SqlClient.SqlException(Date_Format)

I am using Sql-Server 2012 and have a query

string sqlQuery = "SELECT distinct DATE_FORMAT(collectiondate,'%m/%d/%Y') FROM reports  where patientid= " + patientId + " and isdeleted=false order by collectiondate desc";
            var lst = Session.CreateSQLQuery(sqlQuery).List();
            ArrayList rpt = new ArrayList();
            rpt.Add("--ALL--");

but I am getting an error 

System.Data.SqlClient.SqlException: 'DATE_FORMAT' is not a recognized built-in function name.

Can someone help me out?

Upvotes: 0

Views: 778

Answers (3)

RomSteady
RomSteady

Reputation: 398

You can use the FORMAT() command in SQL Server 2012 instead.

DECLARE @d DATETIME = GETDATE();
SELECT  FORMAT(@d, 'mm/dd/yy', 'en-US') AS 'Result';

Upvotes: 0

Szymon
Szymon

Reputation: 43023

There's no DATE_FORMAT function in SQL Server.

You need to use CONVERT function 

SELECT distinct SELECT CONVERT(VARCHAR(8), collectiondate, 1) ...

Also, formatting is best to be done in the code, not in SQL Server. You should return your date as DATE or DATETIME column and format it in your code.

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166386

You should rather try using CONVERT

SELECT CONVERT(VARCHAR(8),GETDATE(),1)

SQL Fiddle DEMO

That being said, I would recomend returning the values as is from the Database, and leaving the formatting to the UI.

Upvotes: 2

Related Questions