MS SQL + date

Overthere
Overthere used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer

Commented:
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 PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Convert to
YYYYmmdd
no punctuation at all in the date.  This is always 100% accurate in SQL Server, whereas any dashes are not.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.
Ensure you’re charging the right price for your IT

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

Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
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')

Author

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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial