I am using MS SQL 2014 and the database in 2008 capability mode. I am revising a classical ASP page.
I have a table in which I need to insert a date. The column is defined as a DateTime. The dates are formatted by using dashes instead of slashes i.e. 2017-06-23 00:00:00
I can change the users input to YYYY-mm-dd just fine. And my update statement works fine EXCEPT that the inserted date is screwy.
I know this can happen with datetime fields but how can I fix it? Any help appreciated.
Below I have posted the convert date format routine, the sql update statement and how it looks in the table afterwards.
This converts the format to YYYY-mm-dd and works fine.
'incoming format is MMDDYYYY -
' Needed converted format is: YYYY-mm-dd
chgmonth = cstr(mid(xresdate,1,2))
chgday = cstr(mid(xresdate,3,2))
chgyear = cstr(mid(xresdate,5,4))
chgdate = cstr(chgyear) & "-" & cstr(chgmonth) & "-" & cstr(chgday)
This is my update statement copied and its works fine except how the date is inserted into the column in the table - This is the actual display from my response.write statement (the date/payment amt and paymentid are variables in the sqltxt)
Update Payments set PymtRevRegDate=2016-10-06,PaymentAmt=1986.22 where PaymentId=2662
this is what the date column looks like in the database after executing the update statement
I understand that what I see visually in database aren't how it's internally stored etc. But all the other dates in that column from the old version are correct i.e. YYYY-mm-dd and have a timestamp.