Last post Jun 07, 2020 02:40 AM by sking
Jun 06, 2020 06:20 PM|sking|LINK
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
Is there a way to use this #112 format in the tableadapter? Or, can this be done in the code behind if preferably?
Jun 06, 2020 08:30 PM|mgebhard|LINK
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.
DECLARE @date DATE;
SET @date = '';
If you want to change the date from 1900-01-01 to 19000101 using TSQL, the syntax is covered in the FORMAT reference documentation.
SELECT FORMAT(@date, 'yyyyMMdd', 'en-US');
In C# the format is similar and the formats are covered the official C# docs as well.
Jun 07, 2020 02:40 AM|sking|LINK
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
V09A = "09A" + tblloanappsrow.borssn + Space(8)
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
V09Acb = "09A" + tblloanappsrow.cborssn + Space(8)