Date time problem MSSQL 2008 & ASP Classic

Suzanne Rowley
Suzanne Rowley used Ask the Experts™
on
I know what the problem is why its happening and whats going wrong, i have run out of ideas to correct it..

Setting The Scene...

i have a page where there are 3 dates, Creation Date, Expected Completion Date, and Actual Completion Date..

Not getting any problems with the first 2 as they are populated at the same time, and from then on their stat is a known quantity.

the third date is causing me no end of problems, the page can be updated numerous times but when the Actual Completion Date is filled in using a date picker. i need different correction code to enter it into the database. this is fine, but if the page gets updated again, it needs the same date function as the first 2 dates.

i need a way of telling what format a date is in and correct it accordingly, is this possible and how would i go about it..

Thank you in advance..
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Hi Suzanne

Please provide us a data mockup with sample data of what you are trying to pull off here, as it's hard for us experts to conceptualize what you are asking based on a description.

Thanks in advance.

Author

Commented:
UPDATE WorkOrders SET ShortDescription = 'weekly', EqID = 1, Requestor = 3, ReqDate = '2019/04/10', PriorityID = 1, Status = 'Completed', AuditItem = 0, AuditList = 'PMS', Instructions = 'test', DetDesc = 'test', ActionTaken = '', SupPerID = 0, VerifiedBy = 0, EstHours = 0, ActHours = 0, ExpCompDate = '2019/04/22', ActCompDate = '23/2019/04' where WoID = 1
Microsoft SQL Server Native Client 10.0 error '80040e07'

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

/cmms/common/db_obj.asp, line 304

Author

Commented:
if you can see, the date for Actual completion date is showing wrong ActCompDate = '23/2019/04

this is because its using the function for an insert when its an update, the insert function works when the date is first input, but any updates to the page after that gives the error.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Couple of possibilities..

>ExpCompDate = '2019/04/22', ActCompDate = '23/2019/04'
(Likely) You're mixing the date formats with YYYY MM DD, and it's possible that one of these formats is not being successfully interpreted by SQL Server as a date, therefore it thinks it's a varchar, hence the error.   Better to use all 'YYYYMMDD' formats in code.

One of the other columns is a date, and you're trying to insert a value that is a non-date such as '1'.

Author

Commented:
it is because the year month day are in the wrong order..

that is my problem i need a way of telling what order they are in so i can correct accordingly, the date it is failing on is only entered during an update, i need to use the insertdate function on that one and it works, but the form can be updated after this so then i need to use my updatedate function, but i have no way of telling what is needed programmatically

Author

Commented:
the date could even be changed, then it would be back to the insertdate function
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Define 'insertdate function', as I don't see a function anywhere in the T-SQL.
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
Hi Suzanne,

How this date is being built on client side? or in ASP 3.0 classic? Can you share that code please?

Regards,
Chinmay.
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
More than likely your issue is a front end problem.  If you are using classic asp the date is expected to be in the format of m/d/yyyy or yyyy/mm/dd. Either option will end up in sql server as yyyy/mm/dd.

This is a front end problem with your datepicker. There should be a setting in your date picker to format the date and that is what I would start with.  Which datepicker are you using?

Author

Commented:
you are spot on there, the date needs to display in the uk format, this is how i have the date picker set the date will always be in 1 of 2 formats is there a way to read the var see where the yyyy is in it, from that i will know what datefunction to use and then i can use an if else to use the correct one..

the 2 date formats i need to check are mm/dd/yyyy and dd/yyyy/mm
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
I can't verify right now but I wonder if setting the locale will help with the date issue

http://www.4guysfromrolla.com/webtech/031401-1.2.shtml
https://www.w3schools.com/asp/prop_lcid.asp

Author

Commented:
anyone got anything else that may help....
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
What suggestions have you tried so far?  

Chinmay asked to see your example asp code and if you are using classic asp or .net.  Can you provide those details?

If SQL server stores the data as yyyy-MM-dd and classic asp can successfully post date from m/d/yyyy to sql server that is because of the locality. I don't have a way to test it but changing your locality in classic asp may do the trick.  If not, you can update your date picker to display one format but submit another.

Post the html/asp code for your form or at least your date picker and make sure to include which date picker you are using.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Echoing other experts here and providing my own less-then-official color commentary, I believe the appropriate solution would be to locate the front-end developer that wrote the code that allows date pickers to accept varying formats for dates, give them a swift slap upside the head, and inform them that they have a prod support ticket with their name on it to fix the dates so that they are all the same format.

It is not the role of the database to intake dates in wildly different formats and to 'guess' what format it really is, because that would mean if the 'guess' is wrong now you are now responsible for bad data.   For example,  08/10/1999 - August 10th or October 8th ?   No idea.

Yes you can write functions to deal with this, and the above experts have provided helpful info to do that.  No I really do not recommend going down this road.

Good luck.
Jim

Author

Commented:
I dont like the road much, but unless i rewrite the whole thing in a different language this is the only road that gets me where i am going...

Author

Commented:
Jim Horn.. sorry, i know why this is happening as stated in the opening post, my problem is that it can be both an insert or an update, my insert date function wont work on an update and visa versa, the date in question can be a new insert buried in the actual form update..

i need to be able to read the var determine what position the year is in.

this i imagine would be dome by counting the chars between the /  / , i can then use the result to use the correct function with an if then else statement...

i am asking for help to do this...


i will look at my date picker, displaying and sending different formats sounds like a winner..
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
As example, the bootstrap date picker https://bootstrap-datepicker.readthedocs.io/en/latest/

You can set it using js
$('.datepicker').datepicker({
    format: 'mm/dd/yyyy',
    startDate: '-3d'
});

Open in new window

or data- attribute
<input class="datepicker" data-date-format="mm/dd/yyyy">

Open in new window




https://bootstrap-datepicker.readthedocs.io/en/latest/i18n.html?highlight=format

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