locked
Get only date value RRS feed

  • Question

  • User1997423929 posted
     SqlDataAdapter da = new SqlDataAdapter("select no,CONVERT(DATE,CDate) as Create_Date,Itime,OTime from App where ID=@ID", sql_con);
    
                        da.SelectCommand.Parameters.Add("@ID", SqlDbType.VarChar);
                        da.SelectCommand.Parameters["@ID"].Value = ID;
                        
    
                        da.Fill(ds);


    I want to get only the date value from this datetime "2018-11-05 00:00:00.000". When I am running the sql statement("select no,CONVERT(DATE,CDate) as Create_Date,Itime,OTime from App") in sql server it returns the only date value but in dataset it is displaying the whole date and time value.

    Tuesday, January 22, 2019 8:04 AM

Answers

  • User1520731567 posted

    Hi Adwin Jha,

    select no,CONVERT(DATE,CDate) as Create_Date,Itime,OTime from App where ID=@ID
    

    Your conversion type DATE limits the display of the data.

    I suggest you could modify the code,like:

    CONVERT( data_type(length), data_to_be_converted, style )

    111  yyyy/mm/dd
    select CONVERT(VARCHAR(10),[SampleDateTime],111) as Create_Date...

    How it works in C#:

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 23, 2019 6:05 AM

All replies

  • User753101303 posted

    Hi,

    You have to distinguish between the actual value and how it is shown (SQL does have a DATE only type which is not the case for C# and anyway formatting depends as well on which country convention is used).

    It would be best to format the value at the UI level or maybe with attributes. For example you have https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings#the-short-date-d-format-specifier (and note the date is shown as well depending on the country. You can configure the default country you want in the web.config file).

    Tuesday, January 22, 2019 8:31 AM
  • User1997423929 posted

    Hi,

    You have to distinguish between the actual value and how it is shown (SQL does have a DATE only type which is not the case for C# and anyway formatting depends as well on which country convention is used).

    It would be best to format the value at the UI level or maybe with attributes. For example you have https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings#the-short-date-d-format-specifier (and note the date is shown as well depending on the country. You can configure the default country you want in the web.config file).

    Actual value "2018-11-05 00:00:00.000" and after executing sql statement it shown like this "2018-11-05"

    Tuesday, January 22, 2019 9:19 AM
  • User753101303 posted

    It's just how SQL Server Management shows a DATE (rather than a DATETIME) value (C# doesn't have a date only type). If looking at the earlier link you can see that you can show a date using multiple formats and depending on which country. It would be best done at the UI level. For example with a dynamic control it could be :

       <asp:DynamicControl runat="server" DataField="MyDate" DataFormatString="{0:d}"/>

    which would show a short date for the current country. You can set the country in the web.config. The benefit is that you could easily support later multiple languages...

    You'll perhaps see suggestion for doing this date to string conversion on the SQL side butIMO it's best to get a "true" date and format that date to a string when needed (ie when you are about to show this value to a user).

    Tuesday, January 22, 2019 9:30 AM
  • User1520731567 posted

    Hi Adwin Jha,

    select no,CONVERT(DATE,CDate) as Create_Date,Itime,OTime from App where ID=@ID
    

    Your conversion type DATE limits the display of the data.

    I suggest you could modify the code,like:

    CONVERT( data_type(length), data_to_be_converted, style )

    111  yyyy/mm/dd
    select CONVERT(VARCHAR(10),[SampleDateTime],111) as Create_Date...

    How it works in C#:

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 23, 2019 6:05 AM