如何获取局域网内sqlserver2000服务器的日期。

[liqiang208]

我用ado连接到sqlserver，在查询想用服务器的日期，怎么办？谢谢

[pcm112]

Option Explicit
'
'
Private Declare Function NetRemoteTOD Lib "Netapi32.dll" ( _
tServer As Any, pBuffer As Long) As Long
'
Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type
'
Private Type TIME_ZONE_INFORMATION
Bias As Long
StandardName(32) As Integer
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(32) As Integer
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type
'
private Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneIn
formation as TIME_ZONE_INFORMATION) as Long
'
private Declare Function NetApiBufferFree Lib "Netapi32.dll" (byval lpBuffer
as Long) as Long
'
Private Type TIME_OF_DAY_INFO
tod_elapsedt As Long
tod_msecs As Long
tod_hours As Long
tod_mins As Long
tod_secs As Long
tod_hunds As Long
tod_timezone As Long
tod_tinterval As Long
tod_day As Long
tod_month As Long
tod_year As Long
tod_weekday As Long
End Type
'
private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destina
tion as Any, Source as Any, byval Length as Long)
'
'
Public Function getRemoteTOD(ByVal strServer As String) As Date
'
Dim result As Date
Dim lRet As Long
Dim tod As TIME_OF_DAY_INFO
Dim lpbuff As Long
Dim tServer() As Byte
'
tServer = strServer & vbNullChar
lRet = NetRemoteTOD(tServer(0), lpbuff)
'
If lRet = 0 Then
CopyMemory tod, ByVal lpbuff, Len(tod)
NetApiBufferFree lpbuff
　　　　result = DateSerial(tod.tod_year, tod.tod_month, tod.tod_day) + _
　　　　TimeSerial(tod.tod_hours, tod.tod_mins - tod.tod_timezone, tod.tod_s
ecs)
getRemoteTOD = result
Else
Err.Raise Number:=vbObjectError + 1001, _
Description:="cannot get remote TOD"
End If
'
End Function
'要运行该程序，通过如下方式调用。
Private Sub Command1_Click()
Dim d As Date
'
d = getRemoteTOD("your NT server name goes here")
MsgBox d
End Sub
'

Private Sub Form_Load()

End Sub

[online]

'引用microsoft activex data object 2.x library
Option Explicit
Private conn As ADODB.Connection
Private rs As ADODB.Recordset

Private Sub Form_Load()
Dim apppath As String
Dim dbfilename As String
Dim ConnectString As String
Dim i As Integer
Set conn = New ADODB.Connection
ConnectString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=northwind;Data Source=yang"
conn.Open ConnectString
conn.CursorLocation = adUseClient

Set rs = New ADODB.Recordset
看这里
rs.Open "select getdate() as sj", conn, adOpenKeyset, adLockOptimistic
msgbox rs.fields("sj")
End Sub

[wumylove1234]

Public Function GetServerDate() As Date
'*****************************************
' '获取服务器时间的函数
'*****************************************
On Error GoTo errhandle
Dim Cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim para1 As ADODB.Parameter
Set Cnn = New ADODB.Connection
Set para1 = New ADODB.Parameter
Set cmd = New ADODB.Command
Cnn.ConnectionString = ConnectString
Cnn.Open
cmd.CommandText = "my_get_severtime"
cmd.ActiveConnection = Cnn
cmd.CommandType = adCmdStoredProc
Set para1 = cmd.CreateParameter("mydate", adDate, adParamOutput)
cmd.Parameters.Append para1
cmd.Execute
GetServerDate = cmd.Parameters("mydate")
Set Cnn = Nothing
Set cmd = Nothing
Set para1 = Nothing
Exit Function
errhandle: '如果获得服务器时间出错,则调用本地时间
GetServerDate = Date
Set Cnn = Nothing
Set cmd = Nothing
Set para1 = Nothing
End Function
Public Function GetServerTime() As Date
'***************************************
'取服务器时间
'***************************************
Dim Sqlstring As String
Dim Rst As ADODB.Recordset
Set Rst = New ADODB.Recordset
Sqlstring = "select substring(convert(varchar(20),getdate(),20),12,9)"
Set Rst = ExecuteSQL(Sqlstring)
On Error GoTo errhandle
GetServerTime = CDate(Rst.Fields(0).Value)
Rst.Close
Set Rst = Nothing
errhandle: '出错取本地时间
GetServerTime = Time
Set Cnn = Nothing
End Function

[pcm112]

顶一下！！

[apple800]

简单点：用ado连接到sqlserver后，select getdate() 就是服务器的时间

[DebugXP]

直接rs.open "select GetDate()"得到的就是服务器时间

[zyg0]

select getdate()

[huangjianyou]

一个比一个简单。

^_^

[sharpidea]

神经病啊，那么麻烦！
rs.open"select getdate()",conn,.....
msgbox rs(0).value
就是拉

[flyingZFX]

select getdate()

这简直太容易了，

真是难的不会，会的不难呀，

[email0755]

呵呵！简单问题复杂化！复杂问题简单化！
标准答案都一大把。。。。。。。。

[pcm112]

haha!!

[eagle008]

当然是用 Select getdate() 最方便了

[TBNTB]

学习中,各位老大历害

[wumylove1234]

取出来的又有日期,又有时间.
写个函数把二者分开(如果想要时间的话)