formatting dates in SQL (Pure ASP)

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")

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.

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?

Consider using a trigger on your table to track this information.

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...

If I am not wrong, you are expecting this.

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

Select allOpen in new window

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?

*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

@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.

@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).