How does “select getdate()” get the time?

getdate is an interesting function to look at as it is some data that sql has to ask the o/s for, it isn’t something that it can (or should) store itself.

If we look at the windows api’s that it could possible use, we have these possibilities:

 

GetLocalTime

GetSystemTime

GetSystemTimeAsFileTime (Sounds unlikely)

GetTickCount (Unlikely but would probably be used if sql stores the startup time and the current time is based from that – let’s hope not!)

 

Using cdb, we get this stack trace (Sql 2012 32 bit):

KERNELBASE!GetLocalTime (FPO: [1,17,4])
sqllang!utgettime+0x10f (FPO: [2,23,4])
sqllang!D8GetDate+0x10 (FPO: [0,0,4])
sqlTsEs!CEsExec::GeneralEval4+0xbc (FPO: [Non-Fpo])
sqllang!CXStmtAssignBase::XretExecute+0x153 (FPO: [Non-Fpo])
sqllang!CXStmtSelectWithoutQuery::XretExecute+0x129 (FPO: [Non-Fpo])
sqllang!CMsqlExecContext::ExecuteStmts<1,1>+0x352 (FPO: [Non-Fpo])
sqllang!CMsqlExecContext::FExecute+0x878 (FPO: [Non-Fpo])
sqllang!CSQLSource::Execute+0x7d5 (FPO: [3,41,0])
sqllang!process_request+0x3fa (FPO: [Non-Fpo])
sqllang!process_commands+0x38c (FPO: [Non-Fpo])
sqldk!SOS_Task::Param::Execute+0x292 (FPO: [Non-Fpo])
sqldk!SOS_Scheduler::RunTask+0xa2 (FPO: [Non-Fpo])
sqldk!SOS_Scheduler::ProcessTasks+0x316 (FPO: [2,15,0])
sqldk!SchedulerManager::WorkerEntryPoint+0x2e7 (FPO: [Non-Fpo])
sqldk!SystemThread::RunWorker+0xae (FPO: [Non-Fpo])
sqldk!SystemThreadDispatcher::ProcessWorker+0x2fe (FPO: [2,21,0])
sqldk!SchedulerManager::ThreadEntryPoint+0x20b (FPO: [Non-Fpo])
KERNEL32!BaseThreadInitThunk+0xe (FPO: [Non-Fpo])
ntdll!__RtlUserThreadStart+0x20 (FPO: [SEH])
ntdll!_RtlUserThreadStart+0x1b (FPO: [Non-Fpo])

 

Interesting things here:

sqllang!CXStmtSelectWithoutQuery::XretExecute – Run a select without an actual query.

sqllang!D8GetDate – This makes sense as it returns a datetime which is 8 bytes, I couldn’t find any other D ?? GetDate functions using the public symbols.

There are two variants of GetDate in sqllang, D8GetDate and D8GetDateUTC – they are small functions and D8GetDate passes a 0 to sqllang!utgettime, D8GetDateUTC passes a 1. (There is also a D8GetDateOnly which also passes a 1 so asks for the UTC date).

utgettime takes a pointer to a SQLDATE (SQLDATE*) and an int, the int (as mentioned) determines which date to ask the o/s for.

utgettime uses the 1 or 0 to determine whether it calls GetLocalTime (0 non UTC) or GetSystemTime (1 UTC)

select getutcdate() obviously resolves to utgettime( &date, 1)

 

So there you have it, GetDate() uses the windows kernelbase (forwarded from kernel32) GetLocalTime and GetUTCDate() uses GetSystemTime

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s