locked
Convert a Date to YYYYMMDD RRS feed

  • Question

  • User-1767698477 posted

    When I store values to sql db I use a blank (cmdinsert.Parameters.Add(New SqlParameter("@borsigdate", ""))) and if the column is a date, (not a datetime) it slots in 1900-01-01 to the column.  When I retrieve values in my tableadapter, it looks like this #1/1/1900# when I hover my mouse over the thing at a breakpoint. How do I convert this to YYYYMMDD ?  Interestinly, when I covert it to a string, it turns it into 1/1/1900 12:00:00 AM which I don't understand because it is not a datetime. It's just a date.

    I found this page here https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

    Is there a way to use this #112 format in the tableadapter? Or, can this be done in the code behind if preferably?

    Saturday, June 6, 2020 6:20 PM

Answers

All replies

  • User475983607 posted

    The time portion is most likely coming from your C# code which is not included in your problem statement.  The TSQL Date type has a range of 0001-01-01 through 9999-12-31  and a default value of  1900-01-01.  Please read the reference documentation.

    https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql?view=sql-server-ver15

    DECLARE @date DATE;
    SET @date = '';
    SELECT @date;
    1900-01-01

    If you want to change the date from 1900-01-01 to 19000101 using TSQL, the syntax is covered in the FORMAT reference documentation.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15

    SELECT FORMAT(@date, 'yyyyMMdd', 'en-US');

    In C# the format is similar and the formats are covered the official C# docs as well.

    https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

    https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings

    [string].Format("yyyyMMdd").

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 6, 2020 8:30 PM
  • User-1767698477 posted

    Hi, thank  you for the documentation links. I have read through those. I prefer to do the coding in the code behind because I am constantly changing my tableadapter when I add a column here and there. So it is much easier to not have to go and manually add sql code for dates. The following is working for me now. I have added an import for System.Globalization

      If tblloanappsrow.borsigdate.ToString <> "1/1/1900 12:00:00 AM" Then
                    Dim borsigdate As String = tblloanappsrow.borsigdate.ToString("yyyyMMdd", CultureInfo.InvariantCulture)
                    V09A = "09A" + tblloanappsrow.borssn + borsigdate
                Else
                    V09A = "09A" + tblloanappsrow.borssn + Space(8)
                End If
                If tblloanappsrow.cborsigdate.ToString <> "1/1/1900 12:00:00 AM" Then
                    Dim cborsigdate As String = tblloanappsrow.cborsigdate.ToString("yyyyMMdd", CultureInfo.InvariantCulture)
                    V09Acb = "09A" + tblloanappsrow.cborssn + cborsigdate
                Else
                    V09Acb = "09A" + tblloanappsrow.cborssn + Space(8)
                End If

    Sunday, June 7, 2020 2:40 AM