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.
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)
this is what the date column looks like in the database after executing the update statement
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.
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.
Convert to
YYYYmmdd
no punctuation at all in the date. This is always 100% accurate in SQL Server, whereas any dashes are not.
YYYYmmdd
no punctuation at all in the date. This is always 100% accurate in SQL Server, whereas any dashes are not.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
I thank all of you. It's working now just fine!
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.