Returning Current Time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Returning Current Time

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-03 : 08:19:42

Ricky writes "I have a VB6 application using ADO connection to SQL Server 2000. It is a Time and Attendance package so it is very important that I pull the time from the same place everytime a transaction occurs. Problems ensue when I use this function because it uses the windows username to go to the server for the Time of Day and I do not want to have to add every user to the Server. The function itself does not allow me to specify to use the existing connection to the Server.

D = getRemoteTOD(ServerName)

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_secs)
getRemoteTOD = result
Else
Err.Raise Number:=vbObjectError + 1001, _
Description:="Cannot TOD from the Server"
End If

End Function

Question: Is there anyway to get the Date and Time from the server using the same connection that the Application is using?"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-03 : 08:35:22

What part of SQL Server is this then??

In SQL you would use select getdate()

ClayCo68
Starting Member

2 Posts

Posted - 2004-06-09 : 10:07:43

I sent this to the great guys here at SQLTeam.com before I had a username and password, so yes, my real name is Ricky.

This is happening at runtime in the VB6 application on individual workstations connected to the network. How can I put the GetDate() function into a "Select" statement or How can I tell the GetDate() function which SQL Server connection to choose from?

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2004-06-09 : 10:19:31

We had the same issue and determine we always wanted to use the database data and time through our various tiers. So we simply wrote a procedure to return the datetime value from the database like so:

USE <your database>
GO

IF OBJECT_ID('dbo.<pick your own name>') IS NOT NULL
	DROP PROCEDURE dbo.<pick your own name>
GO


CREATE PROCEDURE dbo.<pick your own name>
(
    @DateTime DATETIME OUTPUT
)
AS
SET @DateTime = GETDATE()
GO

GRANT ALL ON dbo.<pick your own name> TO PUBLIC
GO

Just call this procedure from your application. Better yet, make a utility procedure that wraps this call and just all that method from your code. This had to be a procedure instead of a function though since the use of GETDATE() is not allowed in functions (deterministic vs. non-deterministic stuff).

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-09 : 12:06:45

What does the function NetRemoteTOD() do?

The function you provided does not connect to SQL Server. You mention you already have a connection.
Depending on how the connection is used, and when you want to get TOD, you may or may not be able to use the same connection. If you attempt to SELECT GETDATE() on a connection with a pending transaction or open resultset you will get an error in your app.

ClayCo68
Starting Member

2 Posts

Posted - 2004-06-14 : 12:31:43

tfountain - Thanks I will give it a try.

kselvia - In the example that I provided, D = getRemoteTOD(ServerName), ServerName = is the IP address of the server that has SQL Server installed on it. The actual connection to SQL Server only occurs when the application starts or is being used by the employees, however I want to return the time every five seconds so that when the employees are clocking in and out they see an accurate representation of what thier time card is going to show. Actually connecting to SQL Server every five seconds or leaving the connection open is not really an option due to the number of people that will be using the system. I mainly hope to get the time from the same place that SQL Server would draw it from which is the actual server itself without the overhead of a full blown SQL connection.

Now that I've written it out, it seems that this is more of a VB6 issue than an SQL one but I do appreciate all the help and info. I will post my results to this anyway(if it is allowed to stay) so that it can be concluded.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources