How to fix the datetime to date only

Question

This is my view

SELECT        AmenityValidDatesPriceID, AmenityID, AmenityValidDatesFrom, AmenityValidDatesTo, AddedDate, LastModifyDate, AddedUser, LastModifyUser, 
                         IsDeleted
FROM            dbo.DataManagement_Hotels_Amenities_AmenityValidDatesPrice
WHERE        (IsDeleted = 0)

It returns me AmenityValidDatesFrom,AmenityValidDatesTo as datetime

and at front end I just need date

    public List<BLL.Entities.DataManagement_Hotels_Amenities_AmenityValidDatesPrice> SelectByAmenityID(int ID)
            {
                DAL.DynamicSearchViewDataContext Viewcontext = new DAL.DynamicSearchViewDataContext();
                DAL.SelectSectionDataContext SpContext = new DAL.SelectSectionDataContext();
                var Query = (from car in Viewcontext.View_DataManagement_Hotel_Amenities_Dates
                             where car.AmenityID.Equals(ID)
                             select new BLL.Entities.DataManagement_Hotels_Amenities_AmenityValidDatesPrice
                             {
                                 AmenityID = (int)car.AmenityID,
                                 //AmenityValidDatesFrom = (DateTime)car.AmenityValidDatesFrom,
                                 //AmenityValidDatesTo = (DateTime)car.AmenityValidDatesTo

                                 AmenityValidDatesFrom = ((DateTime)car.AmenityValidDatesFrom).Date,
                                 AmenityValidDatesTo = ((DateTime)car.AmenityValidDatesTo).Date


                             }).ToList<BLL.Entities.DataManagement_Hotels_Amenities_AmenityValidDatesPrice>();
                return Query;

            }

This is my code

It give

error implicitly conversion

possible duplicate of [How can I truncate a datetime in SQL Server?](http://stackoverflow.com/questions/923295/how-can-i-truncate-a-datetime-in-sql-server) — Klas Lindbäck, Feb 08 '13 at 12:21
What datatype is `car.AmenityValidDatesFrom`? What is it's value when you get the error? — stuartd, Feb 08 '13 at 12:23

Arindam Rudra · Answer 1 · 2013-02-08T12:38:54.213

Please alter your view like below:

SELECT  AmenityValidDatesPriceID,
        AmenityID,
        CONVERT(VARCHAR, AmenityValidDatesFrom,101) AS AmenityValidDatesFrom,
        CONVERT(VARCHAR, AmenityValidDatesTo,101) AS AmenityValidDatesTo,
        CONVERT(VARCHAR, AddedDate,101) AS AddedDate,
        CONVERT(VARCHAR, LastModifyDate,101) AS LastModifyDate,
        AddedUser,
        LastModifyUser,
        IsDeleted
FROM    dbo.DataManagement_Hotels_Amenities_AmenityValidDatesPrice
WHERE   IsDeleted = 0

It should work.

Update:

null checking is required.

public List<BLL.Entities.DataManagement_Hotels_Amenities_AmenityValidDatesPrice> SelectByAmenityID(int ID)
{
    DAL.DynamicSearchViewDataContext Viewcontext = new DAL.DynamicSearchViewDataContext();
    DAL.SelectSectionDataContext SpContext = new DAL.SelectSectionDataContext();
    var Query = (from car in Viewcontext.View_DataManagement_Hotel_Amenities_Dates
                 where car.AmenityID.Equals(ID)
                 select new BLL.Entities.DataManagement_Hotels_Amenities_AmenityValidDatesPrice
                 {
                     AmenityID = (int)car.AmenityID,
                     if (car.AmenityValidDatesFrom != null && car.AmenityValidDatesFrom.ToString() !=  "")
                     {
                        AmenityValidDatesFrom = car.AmenityValidDatesFrom.Date
                     },
                     if (car.AmenityValidDatesTo != null && car.AmenityValidDatesTo.ToString() !=  "")
                     {
                        AmenityValidDatesTo = car.AmenityValidDatesTo.Date
                     }
                 }).ToList<BLL.Entities.DataManagement_Hotels_Amenities_AmenityValidDatesPrice>();
    return Query;
}

The argument 'value' was the wrong type. Expected 'System.DateTime'. Actual 'System.Nullable`1[System.DateTime]'. this error i got — princ_sameer, Feb 08 '13 at 12:30
Do you have any empty cells or null valued cells among all the date columns? — Arindam Rudra, Feb 08 '13 at 12:32
If null is there, then only AmenityValidDatesFrom = ((DateTime)car.AmenityValidDatesFrom).Date, AmenityValidDatesTo = ((DateTime)car.AmenityValidDatesTo).Date, these two statements will give you error. — Arindam Rudra, Feb 08 '13 at 12:33

score 1 · Answer 2 · answered Feb 08 '13 at 12:26

1

Try this

SELECT  AmenityValidDatesPriceID ,
        AmenityID ,
        CONVERT(VARCHAR(10), AmenityValidDatesFrom, 103) AS AmenityValidDatesFrom ,
        CONVERT(VARCHAR(10), AmenityValidDatesTo, 103) AS AmenityValidDatesTo ,
        CONVERT(VARCHAR(10), AddedDate, 103) AS AddedDate ,
        CONVERT(VARCHAR(10), LastModifyDate, 103) AS LastModifyDate ,
        AddedUser ,
        LastModifyUser ,
        IsDeleted
FROM    dbo.DataManagement_Hotels_Amenities_AmenityValidDatesPrice
WHERE   IsDeleted = 0

it will gives the date in 'dd/mm/yyyy' format. If you want to get the date in 'mm/dd/yyyy' format then use 101 instead of 103

answered Feb 08 '13 at 12:26

Prashant16

1,514
3
18
39

i modify the view.and when i again try to get data it show this error in code The argument 'value' was the wrong type. Expected 'System.DateTime'. Actual 'System.Nullable`1[System.DateTime]'. – princ_sameer Feb 08 '13 at 12:32
This will allow for the nullable type: `car.AmenityValidDatesFrom.GetValueOrDefault().Date` – stuartd Feb 08 '13 at 12:39

score 0 · Answer 3 · answered Feb 08 '13 at 12:41

Use this view instead to keep the DATETIME data type, while getting rid of the time part:

SELECT  AmenityValidDatesPriceID ,
        AmenityID ,

        DATEADD(D, 0, DATEDIFF(D, 0, AmenityValidDatesFrom)) AS AmenityValidDatesFrom
        DATEADD(D, 0, DATEDIFF(D, 0, AmenityValidDatesTo)) AS AmenityValidDatesTo
        DATEADD(D, 0, DATEDIFF(D, 0, AddedDate)) AS AddedDate
        DATEADD(D, 0, DATEDIFF(D, 0, LastModifyDate)) AS LastModifyDate

        AddedUser ,
        LastModifyUser ,
        IsDeleted
FROM    dbo.DataManagement_Hotels_Amenities_AmenityValidDatesPrice
WHERE   IsDeleted = 0

How to fix the datetime to date only

3 Answers3