Reading datetime column from ms access database using vb

Question

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.

HansUp · Accepted Answer · 2012-10-12 16:31:38Z

2

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;

edited Oct 12, 2012 at 16:31

answered Oct 12, 2012 at 14:45

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sqlchild Over a year ago

how to display current date and time

HansUp Over a year ago

I am very rusty with ASP. Is this close to what you want? FormatDateTime(ctime, 3)

sqlchild Over a year ago

how do I get the date separately

Collectives™ on Stack Overflow

Reading datetime column from ms access database using vb

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related