We help IT Professionals succeed at work.

MS SQL + date

Overthere
Overthere asked
on
176 Views
Last Modified: 2017-03-27
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

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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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
CERTIFIED EXPERT
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.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions