Avatar of Overthere
Overthere
 asked on

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.
Microsoft SQL ServerASP

Avatar of undefined
Last Comment
Overthere

8/22/2022 - Mon
Mike Eghtebas

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.
Scott Pletcher

Convert to
YYYYmmdd
no punctuation at all in the date.  This is always 100% accurate in SQL Server, whereas any dashes are not.
PortletPaul

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Overthere

ASKER
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
Overthere

ASKER
I thank all of you. It's working now just fine!