But mummy, where does time come from?

Question

But mummy, where does time come from?

bleroy

Hall of Fame

Points: 3052
More actions
September 14, 2017 at 9:03 am

#323507

So ... here I was inserting data from one server into another server, and my insert code contains a GETDATE() column (which I simply use as my RecordInsertedDateTime column for auditing/keep my sanity purposes)
But where does the time for the GETDATE() function come from? from the time on the 1st server (which is running the job/code) or from the time on the recipient server (which does the actual inserting).
I hope someone can shine some light on this.
September 15, 2017 at 7:07 am

This was removed by the editor as SPAM
November 4, 2019 at 12:11 pm

This was removed by the editor as SPAM
November 6, 2019 at 12:05 pm

This was removed by the editor as SPAM
April 14, 2020 at 10:49 am

This was removed by the editor as SPAM
September 22, 2020 at 10:43 am

This was removed by the editor as SPAM
September 22, 2020 at 10:44 am

This was removed by the editor as SPAM
September 30, 2020 at 6:18 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

stevenb 14609 SSCommitted Points: 1832 More actions · Answer 1

It's going to be coming from the server that's running the code.
You're telling it to execute the function GETDATE() and send the results of that function to the destination.
Similarly, if you had a function called FOO(), you could reference FOO() on your server where you're running your code without it existing on the target server. It's not telling the target server to run the function FOO() and put the results in the table when you're done.

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 2

SQL Server maintains time itself. However, it syncs periodically (every minute or less) with the underlying host OS, which maintains the time based on hardware crystals and optional NTP (network time) servers. When you execute GETDATE(), SQL Server will return the current time, as it sees it. Therefore, time on the server matters. You can also use sysdatetime(), which is a replacement with more precision.

If I have code on ServerA, which is a batch that is executed on ServerB (say through linked server) ,the batch likely executes on ServerB, which is where the time will come from. The execution context would likely depend on what you're doing, but in general, it's on the server where the code runs. If you share more about the architecture, we can help.

@steve-2 Summers, not sure what you're referring to as you don't discuss GETDATE().

bleroy Hall of Fame Points: 3052 More actions · Answer 3

Thanks very much for the replies! @SteveJones, thanks for the explanation about how sql server deals with time, that explains a few inconsistencies I observed!