troubleshooting Question

MS SQL + date

Avatar of Overthere
Overthere asked on
Microsoft SQL ServerASP
6 Comments1 Solution183 ViewsLast Modified:
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
1905-06-24 00:00:00.000

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.
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros