HELP Re: datetime

[-=SC@RF@C3=-]

Guys i got a problem everytime i save a date in sql it goes like this =
Mon Apr 25 00:00:00 UTC+0800 2005 everytime i show it on the asp page...

The field datatype is declared as datetime. Is there any way i can convert a string to date? I tried using formatedatetime the problem is its function can only be declare as formatdatetime(date(),2) or formatdatetime(now(),2)


what if my textbox is like "6/6/2006"

how can I save it in the field datetime?

[rory]

You are inserting into a database or retrieving from .. and is 6/6/2006 what is inside the database .. or inside a text box on the page ..?

[-=SC@RF@C3=-]

You are inserting into a database or retrieving from .. and is 6/6/2006 what is inside the database .. or inside a text box on the page ..?

i got a text box inputted a date like "6/6/2006"
so 6/6/2006 is in textbox1


I want it to save in sql database.. im using vbscript. when pressing submit button i got errors..... i tried researching regarding formatdatetime function..... but all i see is formatdatetime(now(),1) or formatdatetime(date(),1).... this only shows the current date in your pc right.... but how about if i want to save the ones in the textbox?

[rory]

To set a date such as Date or Now into the format 6/6/2006:
formatdatetime(Date, 2)

To covert a string to a Date Type:
CDate(textbox1)

When inserting into an Access Database Date/Time Field you need to use #'s around the value:
#mydate#

Check the Textbox value like this ..
If IsDate(textbox1) Then

References:
http://www.w3schools.com/vbscript/fu...atdatetime.asp
http://www.w3schools.com/vbscript/func_cdate.asp

[-=SC@RF@C3=-]

To set a date such as Date or Now into the format 6/6/2006:
formatdatetime(Date, 2)

To covert a string to a Date Type:
CDate(textbox1)

When inserting into an Access Database Date/Time Field you need to use #'s around the value:
#mydate#

Check the Textbox value like this ..
If IsDate(textbox1) Then

References:
http://www.w3schools.com/vbscript/fu...atdatetime.asp
http://www.w3schools.com/vbscript/func_cdate.asp

I tried using cdate....

texbox1 = "6/6/06"

date1 = textbox1.value

textboxdate = cdate(date1)

am i right on this? cdate converts string to date..... but then it doesnt work

then i tried this formatdatetime(textboxdate(),2) doesnt work too

[-=SC@RF@C3=-]

im not using the current date im using the date in the textbox inputted to be save in the database

[rory]

Is this ASP or client side VBscript?

FormatDateTime is not required, thats only if you want to format a Date or Time Value such as Date or Now.

[-=SC@RF@C3=-]

Is this ASP or client side VBscript?

FormatDateTime is not required, thats only if you want to format a Date or Time Value such as Date or Now.

It is vbscript. so your saying if i want to format the current date.... not the date i inputted......??


why is it that after i save it.... everytime i show it in a text box it goes like this?

Mon Apr 25 00:00:00 UTC+0800 2005

[rory]

FormatDateTime is only for formatting Correct Dates/Times ..

Eg ..

strDate = FormatDateTime("6/26/2006 1:53:18 AM",2)
strDate = cdate(strDate)
If IsDate(strDate) And err = 0 Then

This will fail:
strDate = FormatDateTime("Mon Apr 25 00:00:00 UTC+0800 2005",2)
strDate = cdate(strDate)
If IsDate(strDate) And err = 0 Then


You will need to also make your Date Field in the Database to Short Date.

Is the Database SQL Server ..? Also is this client side VBscript ..?

[-=SC@RF@C3=-]

yes it is sql server and vbscript

[-=SC@RF@C3=-]

i save it like this 6/6/2006 but when i call it and show it on the text box it shows like this Mon Apr 25 00:00:00 UTC+0800 2005 ill post a screenshot

[rory]

Ok .. so you arent getting any errors though when inserting it into the Database?

When inserting it make it ..

Option Explicit
Dim textboxdate, strSQL
On Error Resume Next
textboxdate = FormatDateTime(textbox1.value, 2)
textboxdate = CDate(textboxdate)
If IsDate(textboxdate) And err = 0 Then
    strSQL = "INSERT INTO myTable (myDate) VALUES (" & textboxdate & ")"
    MsgBox strSQL
Else
    MsgBox "Date Error! " & err.Description 
End If

[rory]

i save it like this 6/6/2006 but when i call it and show it on the text box it shows like this Mon Apr 25 00:00:00 UTC+0800 2005 ill post a screenshot

Can you open the database and see what the actual value is in there ..?

[-=SC@RF@C3=-]

[rory]

also .. dont know how you are getting the textbox value or calling the VBScript Sub to get the value but typically it is done like this in Client Side VBScript ..

myForm.TextBox1.Value

Where myForm is the name of the form and TextBox1 is the name of the Textbox

[-=SC@RF@C3=-]

the value remains the same 6/6/2006 but everytime i call its dbase to show on the textbox that shows up

[rory]

Without seeing all the code ... and knowing a little more about the database .. it is hard for me to say ..

[-=SC@RF@C3=-]

the code is simple as this

textbox1.value = name("srf_date")

srf_date is declared as datetime

[aconybeare]

It's not recommended to use the short style date because it's open to misinterpretation better to use long date (10 Jun 2006) I always use a popup calendar when getting dates from my users that way they can't blow the format. I then pass the long date to SQL that way again there can be no confusion and convert it on the way out of the db.

Here are some examples of formatting dates with SQL Server

-- Source - http://lejalgenes.com/techtips/tips/Microsoft_SQL_Server/Formating_date_in_MSSQL_server.php
PRINT '1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' + CONVERT(CHAR(19),GETDATE())
PRINT '2) HERE IS MM-DD-YY FORMAT ==>' + CONVERT(CHAR(8),GETDATE(),10)
PRINT '3) HERE IS MM-DD-YYYY FORMAT ==>' + CONVERT(CHAR(10),GETDATE(),110)
PRINT '4) HERE IS DD MON YYYY FORMAT ==>' + CONVERT(CHAR(11),GETDATE(),106)
PRINT '5) HERE IS DD MON YY FORMAT ==>' + CONVERT(CHAR(9),GETDATE(),6)
PRINT '6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>' + CONVERT(CHAR(24),GETDATE(),113)
PRINT '7) HERE IS YYYYMONDD FORMAT ==>' + CONVERT(CHAR(8),GETDATE(),112)
PRINT '8) HERE IS YYYY-MON-DDTHH:MM:SS.MMM(24H) FORMAT ==>' + CONVERT(VARCHAR(23),GETDATE(),126)
PRINT '9) HERE IS YYYY-MON-DD HH:MM:SS(24H) FORMAT ==>' + CONVERT(VARCHAR(19),GETDATE(),120)

-- OutPut
-- 1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>May 11 2006  9:34AM
-- 2) HERE IS MM-DD-YY FORMAT ==>05-11-06
-- 3) HERE IS MM-DD-YYYY FORMAT ==>05-11-2006
-- 4) HERE IS DD MON YYYY FORMAT ==>11 May 2006
-- 5) HERE IS DD MON YY FORMAT ==>11 May 06
-- 6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>11 May 2006 09:34:56:317
-- 7) HERE IS YYYYMONDD FORMAT ==>20060511
-- 8) HERE IS YYYY-MON-DDTHH:MM:SS.MMM(24H) FORMAT ==>2006-05-12T12:23:02.390
-- 9) HERE IS YYYY-MON-DD HH:MM:SS(24H) FORMAT ==>2006-05-12 12:23:02


SELECT id, CONVERT(VARCHAR(11),Start_Date,106) As Start_Date
FROM myTable