MS SQL + date

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)

Open in new window



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

Open in new window


this is what the date column looks like in the database after executing the update statement
1905-06-24 00:00:00.000

Open in new window


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.
OverthereAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
How a date is displayed in a table depends on your computer's local setting. The actual value entered into a database is number of seconds from, say Jan 1, 1971 12:00:00. (for example, I will shortly post the exact date number of seconds are calculated from. It varies based on the software in question.

I found this:
The decision to use 1st January 1753 (1753-01-01) as the minimum date value for a datetime in SQL Server goes back to its Sybase origins.
0
Scott PletcherSenior DBACommented:
Convert to
YYYYmmdd
no punctuation at all in the date.  This is always 100% accurate in SQL Server, whereas any dashes are not.
0
PortletPaulEE Topic AdvisorCommented:
1st Jan 1900 is the zero date in MS SQL, and the method of storage is numeric, but not always a number of seconds (otherwise the sub-second time precision of datetime and datetime2 would not be possible and differs by the various temporal data types).

I believe the solution will be to stop treating your input as a string and to use date handling instead, but I'm not an ASP coder so I can't show you how.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott PletcherSenior DBACommented:
The date is a string in SQL; SQL is treating what you entered as numbers.

Update Payments set PymtRevRegDate='20161006',PaymentAmt=1986.22 where PaymentId=2662
/*or, if you insist*/
Update Payments set PymtRevRegDate='2016-10-06',PaymentAmt=1986.22 where PaymentId=2662


numbers: 2016-10-06 = day 2000 = 1905-06-24, viz:
select dateadd(day, 0, 2016-10-06), /* vs. */ dateadd(day, 0, '2016-10-06')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OverthereAuthor Commented:
First, I thank you all three for responding. That's one of the great things about EE - people are willing to help, explain and share knowledge.
Yes, formatting it to YYYYmmdd would be good, less headaches but that is not to be.  boo :{
SO,  it is working well as inserting it as a string. Sigh.
Thank you
0
OverthereAuthor Commented:
I thank all of you. It's working now just fine!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.