CODEWITHSUNDEEP
ms-accessasp-classicvbscriptvba
sqlchild
sqlchild

Reputation: 9084

Reading datetime column from ms access database using vb

I want to read date time from ms access database .

the column is of datatype DATETIME and value is : 1/27/12 5:10 PM

<html>
<body>
<%



Dim Conn
Dim Rs
Dim sql

Dim sConnection


Dim Today as Date //this gives error - unexpected end of file , i want to display current date and time ??? 


Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")

 filepath= Server.MapPath ("mydb.mdb") 
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="  & filepath

Conn.Open(sConnection)



sql="select * from items;"

Set Rs = Conn.Execute(sql)

if Rs.EOF then

    Response.write("No records found")
else 


    do until rs.eof


            ctime =     rs("itemadditiondatetime")

            Response.write("<br/>Time : <input name='dateandtime[]' value=" & ctime & "/>" & ctime )


            rs.movenext

    loop

end if

Rs.Close
Conn.Close

Set Rs = Nothing
Set Conn = Nothing


%>
</body>
</html>

In all text boxes only date is displayed ( 1/27/12 ) , time is not being displayed , but when I display without text box then whole datetime is displayed. also how do i get date and time separately in two text boxes.

Also , I need to format date as yyyy-mm-dd.

Upvotes: 0

Views: 2862

Answers (1)

HansUp
HansUp

Reputation: 97131

If this is classic ASP/VBScript:

'Dim Today as Date 
Dim Today ' without declaring data type

You can use the FormatDateTime function to get the date and/or time component from your ctime value.

FormatDateTime(ctime, 3) ' long time
FormatDateTime(ctime, 2) ' short date

See the linked page for other FormatDateTime options in case you prefer different formats than I suggested.

I overlooked "need to format date as yyyy-mm-dd". Unfortunately, VBScript doesn't provide VBA's Format(ctime, "yyyy-mm-dd") feature. So I think you may have to extract the year, month, and day pieces from ctime, and them recombine them in the order you want.

Year(ctime) & "-" & Right("0" & Month(ctime), 2) & _
    "-" & Right("0" & Day(ctime), 2)

I realized you could simplify your ASP code by asking the db engine to give you the date and time pieces formatted as you wish. Although VBScript can't use the Format() function, the db engine can.

SELECT
    Format(itemadditiondatetime, "hh:nn:ss") AS time_string,
    Format(itemadditiondatetime, "yyyy-mm-dd") AS date_string
FROM items;

Upvotes: 2

Related Questions