formatting dates in SQL (Pure ASP)

Hi there,

When a client updates a certain table (filling a form and therefore inserting data into the SQL server database) a certain "LastUpdated" field is filled/updated... this is done with a hidden "LastDate" field in the form, with value "<% Now %>" .

This works fine in almost all situations, filling the LastUpdated field with values such as "2014-08-12 17:31:59.000" (Aug, 12, 2014). The problem is that in some cases this field, in exactly the same day/time is filled with "2014-08-12 17:31:59.000" and, of course, I have problems related to that.

Question: what could I do to modify <% Now %> so that I could guarantee that the FORMAT of the date of Aug, 12, 2014 would always be saved at my database field as "2014-08-12 17:31:59.000" despite any local configuration at my client place?

Thank you!
fskilnikAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak SubburajanDB DeveloperCommented:
How the data is being inserted into database table from the front-end application? Through a Stored procedure or direct Insert from Application?

If you are inserting into database using an SP, then change the date column(the one passed as NOW) in INSERT statement as below:

CONVERT(VARCHAR(24),now_date,113)

OR

Change the NOW format while passing it to DB like this.

DateTime.Now.ToString("yyyy-MM-ddTHH:mm:sss")
0
ste5anSenior DeveloperCommented:
Change the data type of your column to DATETIME or DATETIME2.

Keep in mind that with using a native date/time data type, formatting is a mere front-end (output) issue. It does not affect the storage of the value.
0
fskilnikAuthor Commented:
Hi, Deepak!

Thanks for the support.

I understand your point and now I realise my question was not very-detailed presented.

Request.Form("LastUpdated")  is the problem-related part of the ASP-routine that will populate the <% Now %> value present on the LastUpdated field of the form to the DB.

That put, I guess the  Request.Form("LastUpdated") should be modified, correct?  Something like: DateTime.Request.Form("LastUpdated").ToString("yyyy-MM-ddTHH:mm:sss")  but, of course, not exactly that.

Could you please tell me what to put in the place of this   DateTime.Request.Form("LastUpdated").ToString("yyyy-MM-ddTHH:mm:sss") idea?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

ste5anSenior DeveloperCommented:
Consider using a trigger on your table to track this information.
0
fskilnikAuthor Commented:
Hi ste5an... thanks for joining.  I also understand your point, but at my ASP-page, what at stake is this code:

Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_connect_server_STRING
    MM_editCmd.CommandText = "UPDATE dbo.Datausers SET UserPassword = ?, Active = ?, LastUpdated = ? WHERE userID = ?"
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 15, Request.Form("RealPassword")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 1, Request.Form("active")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 135, 1, -1, Request.Form("LastUpdated")) ' adDBTimeStamp
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 5, 1, -1, MM_IIF(Request.Form("MM_recordId"), Request.Form("MM_recordId"), null)) ' adDouble
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

Therefore I don´t see a place, in this code, where I could change the format. On the other hand, I do not want to try to change the format date at my SQL server database, because the related table has more than 600 records... don´t want to mess with that...
0
Deepak SubburajanDB DeveloperCommented:
If I am not wrong, you are expecting this.

String.Format("{0:MM/dd/yyyy hh:mm:sss}", Request.Form("LastUpdated"))

Open in new window

0
fskilnikAuthor Commented:
Hi Deepak!

I guess you are right, but when I changed this line:

MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 135, 1, -1, Request.Form("LastUpdated"))

to this line:

MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 135, 1, -1, String.Format("{0:MM/dd/yyyy hh:mm:sss}", Request.Form("LastUpdated")))

and tested the page (trying to update the table), I got an internal server error...  in other words, I need to "explain" to the page that when it will insert the "LastUpdated" form field in the DB, it must be put there (at the DB) in the specific Format you mentioned.... how to do this?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you're getting an internal error because String.Format is a .NET function and you're dealing with classic asp, which doesn't give many options for formatting dates.

since you don't want to do this in the database and use the default value for the field and set it to getdate(), then your best option is a custom function:

function formatMyDate( theDate )
    if not isDate ( theDate )          theDate = now     '-- make sure a valid date is passed
 
   formatMyDate = Month( theDate ) & "-" & Day( theDate ) & "-" & Year( theDate ) & " " & Hour( theDate ) & ":" & Minute( theDate ) & ":" & Second( theDate )
end function

Open in new window


then, from your asp code, call the function like:

MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 135, 1, -1, formatMyDate( Request.Form("LastUpdated") ) ) 

Open in new window


The only caveat here is that it'll round the seconds to 2 places and not 3. I don't believe there's an asp solution that'll allow you to get the 3 places, but we can investigate if that's a major road block
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
*sigh*

>>"... guarantee that the FORMAT of the date of Aug, 12, 2014 would always be saved at my database field as "2014-08-12 17:31:59.000" despite any local configuration at my client place?"

SQL Server does NOT save datetime (or date or time or datetime2 or smalldatetime) "in a format"
these data types are stored as sets of integers

This is true of most dbms's  (e.g. in Oracle a date field is 7 integers)
-----

Formats only matter for input and for output (i.e. when humans are involved)
see: SQL Server Date Styles (formats) using CONVERT()
-----

Unless: You are storing date/time information as strings - which is NOT a good thing to do.

more info:
DATE and TIME ... don't be scared, and do it right (the first time)
The ultimate guide to the datetime datatypes
0
fskilnikAuthor Commented:
@BigMonty: thank you for joining.  Your solution worked marvellously, thanks a lot.  It was simple, it was "inside" my limited competence in programming and, let me add, you gave me extra interesting info related on the matter. In other words, full package, no doubt!    :)

Obs.: the problem related to the number of digits is really irrelevant to my purposes, but I am impressed with the respect you put into my question. Amazing!  (It´s an honour to be helped by EE people, really.)

@PortletPaul: I thank you for the info and links and I must tell you that I store date/time information as strings, but when needed, I create views in which I deal with "parts" of these strings as needed, very similar (and even a bit more sofisticated!) to what Big Monty did in the (nice and simple) function he created.  Anyway, I am not a computer pro, far from that, therefore I must deal with my limited knowledge and the fact that I will not have time (not to mention "lack of focus") to become someone like you or BigMonty! All that put, thank you too for the information and for your willing to help!

Regards,
fskilnik.
0
PortletPaulfreelancerCommented:
@fskilnik

Well, thanks for clearing that up. Please remember that whenever requesting assistance on your date/time fields that you mention they are stored as VARCHAR (or whatever the actual data types is).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.