CODEWITHSUNDEEP
c#asp.net-mvc-5
Susan
Susan

Reputation: 421

How to display date from datetime while retrieving the datetime from database in mvc5?

I want to convert the datetime to date format while retrieving the data from database.

My Controller Code

public ActionResult GetDates(VisitorsViewModel VisitorsVM)
{
    try
    {
        string sqlcmd = "Select * from View_VisitorsForm where CONVERT(VisitingDate As Date)  >='" + fromdt + "'and CONVERT( VisitingDate As Date) <= '" + todt + "'";

        con.Open();
        SqlCommand cmd = new SqlCommand(sqlcmd, con);
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        adp.Fill(dt);
    }
    catch (Exception ex)
    {
        throw;
    }

    ReportDocument rpt = new ReportDocument();
    rpt.Load(Server.MapPath("~/Areas/Sales/CrystalReports/rpt_VisitSummaryCrystalReport.rpt"));
    rpt.SetDataSource(dt);
    Stream stream = rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);

    return File(stream, "application/pdf");
}

I want to convert the date format (need only date not with time) while retrieving from database

How do I convert VisitingDate to display just the date(not time) in below query

string sqlcmd = "Select * from View_VisitorsForm where CAST( VisitingDate As Date)  >='" + fromdt + "'and CAST( VisitingDate As Date) <= '" + todt + "'";

enter image description here

The above sql query cant convert the date format, its coming with time. I want only Date . Eg : 12-Mar-16 like this.

Upvotes: 1

Views: 1009

Answers (2)

Рахул Маквана
Рахул Маквана

Reputation: 954

in sql you can use CONVERT() function in SQL query to do the job.

CONVERT(VARCHAR(19),GETDATE())    
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

Above will result in this :

Nov 04 2014 11:45 PM

11-04-14 11-04-2014

04 Nov 14

04 Nov 2014

04 Nov 2014 11:45:34:243

So your query will be :

string sqlcmd = "Select Column1,Column2,Column3,CONVERT(VARCHAR(10),VisitingDate,110) as 'VisitingDate' from View_VisitorsForm where CONVERT(VARCHAR(10),VisitingDate,110)  >='" + fromdt + "'and CONVERT(VARCHAR(10),VisitingDate,110) <= '" + todt + "'";

Here is your problem, you need to select all columns and for the date column do this. so in the query replace Column1,column2 with your column names.

See w3Schools tutorial here

Upvotes: 0

Mostafiz
Mostafiz

Reputation: 7352

Use ToString to get only date, suppose you have DateTime type in date variable then you can get by this

string s = date.ToString("dd/MM/yyyy");

if you are using sql server 2008 or higher then you can get only date by CONVERT(VisitingDate, getdate())

string sqlcmd = "Select * from View_VisitorsForm where CONVERT(VisitingDate, getdate(),110)  >='" + fromdt + "'and CONVERT(VisitingDate, getdate(),110) <= '" + todt + "'";

Upvotes: 1

Related Questions