CODEWITHSUNDEEP
sqlsql-servert-sql
Nafiz Chow
Nafiz Chow

Reputation: 34

Changing datetime to date data type?

ALTER PROCEDURE [Deal].[USP_LoadDealHistoryByDealId]
    (@DealId int,
     @chk int)
AS
    IF (@chk = 1)
    BEGIN 
        SELECT 
            DDT.FieldType AS field,
            DDT.OldValue as old,
            DDT.NewValue as new,
            AU.FullName AS Name,
            DDT.UpdateOn AS UpdatedOn
        FROM 
            [Deal].[DealTransaction] as DDT
        INNER JOIN
            AD.Users as AU ON AU.UserId = DDT.[UpdateBy]
        WHERE
            DDT.dealId = @DealId
    END
    ELSE if(@chk = 2)
    BEGIN 
        SELECT 
            'No Data' as field ,
            0 as old ,
            0 as new ,
            AU.FullName AS Name,
            DD.UpdatedOn AS UpdatedOn
        FROM  
            AD.Users as AU
        INNER JOIN 
            [Deal].[Deals] AS DD ON AU.UserId = DD.UploadedBy
        WHERE
            DD.dealId = @DealId
    END

UpdatedOn column is of datatype datetime and it shows to my HTML table like 10-09-1992/12.00.... But I want to show just date like 10-09-1992, what should I do?

Here Is my HTML table 

public string LoadDealHistory(DealHistory aDealhistory)
{
        StringBuilder html = new StringBuilder();

        try {
            using (DealTranctionGateway dealTranctionGateway = new DealTranctionGateway())
            {
                DataTable dt = new DataTable();
                dt = dealTranctionGateway.LoadDealHistory(aDealhistory);

                string dealHistory = "";

                if (dt.Rows.Count > 0)
                {
                    html.Append("<table class='table table-bordered'>");
                    html.Append("<thead>");
                    html.Append("<tr>");
                    html.Append("<th>S.No</th>");
                    html.Append("<th>FieldType</th>");
                    html.Append("<th>OldValue</th>");
                    html.Append("<th>NewValue</th>");
                    html.Append("<th>Full Name</th>");
                    html.Append("<th>UpdateOn</th>");
                    html.Append("</thead>");
                    html.Append("</tr>");

                    for (int i = 0; i < dt.Rows.Count; i = i + 1)
                    {
                        int sNo = i + 1;
                        html.Append("<tr>");
                        html.Append("<td>" + sNo + "</td>");
                        html.Append("<td>" + dt.Rows[i]["field"] + "</td>");
                        html.Append("<td>" + dt.Rows[i]["old"] + "</td>");
                        html.Append("<td>" + dt.Rows[i]["new"] + "</td>");
                        html.Append("<td>" + dt.Rows[i]["Name"] + "</td>");
                        html.Append("<td>" + dt.Rows[i]["UpdatedOn"] + "</td>");
                        html.Append("</tr>");
                    }

                    html.Append("</tbody>");
                    html.Append("</table>");
                }
            }
        }
        catch (Exception ex)
        {
        }

        return html.ToString();
    }
}

Upvotes: 1

Views: 80

Answers (3)

Tarun Gupta
Tarun Gupta

Reputation: 307

Select convert(date,getdate())

Upvotes: 0

gofr1
gofr1

Reputation: 15977

You can convert it to string like:

 dt.Rows[i]["UpdatedOn"].ToString("dd-MM-yyyy")

No need for any changes on SQL Server side, date formatting is a front-end problem.

Upvotes: 0

Ave
Ave

Reputation: 4430

Updated:

Tried this:

string updateOn = "";
updateOn = ((DateTime)dt.Rows[i]["UpdatedOn"]).ToString("dd-MM-yyyy", CultureInfo.InvariantCulture);
html.Append("<td>" + updateOn + "</td>");

OR

You can use format DateTime in SQL:

More format at here.

In your case, it should be using style 105.

SELECT replace(convert(NVARCHAR, UpdatedOn, 105), ' ', '-')

Upvotes: 1

Related Questions