get database datetime with ADO

[Aage Eilertsen]

I'm trying to get the (mssql 7.0)database date, and has a ADO connection to the database.
How can I use ADO to receive the date.
(In sql I can write 'select CURRENT_TIMESTAMP' but how can i receive the raw sql output from ADO)

[Lothar Haensler]

dim rs as adodb.recordset
rs.open "select getdate()"
msgbox rs.fields(0) ' contains current date
rs.close

[Aage Eilertsen]

Thanks!
The result is very good, but the returning date is formatted, and it is not Y2K safe (not good to show customers). Do You have any solution on this problem (without lots of string handlings...)
- I wold prefer the result from the query analyzer (1999-07-29 08:45:21.623) in stead of the result from ADO (7/29/99 08:49:54)
Suggestions? (thanks!)

[Lothar Haensler]

this works, but is not optimized.

select convert(varchar(4), datepart(year, getdate())) + "-"
+ convert(varchar(2),datepart(month, getdate())) + "-"
+ convert(varchar(2),datepart(day, getdate())) + " "
+ convert(varchar(2),datepart(hour, getdate())) + ":"
+ convert(varchar(2),datepart(minute, getdate())) + ":"
+ convert(varchar(2),datepart(second, getdate()))

improve it by calling getdate() only once and also include padding with leading zeros if you want.

[Lothar Haensler]

Ok, I haven't seen your request for "without lots of string handling".
Here is a solution - I do it on the client in VB


de.Command1
MsgBox Format(de.rsCommand1.Fields(0), "YYYY-MM-DD HH:MM")
de.rsCommand1.Close



I used the DataEnvironment this time. Command1 is still only a "select getdate()"