Menu
Is it possible to get something C# like DateTime.Ticks in t-sql ?
thanks for help
724
A Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is simply the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC). Therefore, in SQL Server we can use a couple of T-SQL functions to return this. SQL Server Unix Timestamp
This function returns the current database system timestamp as a datetimevalue, without the database time zone offset. CURRENT_TIMESTAMPderives this value from the operating system of the computer on which the instance of SQL Server runs. Note
But sometimes things are out of your control. If you do want to convert between datetime2 and ticks inside SQL Server instead of letting the application handle it, then you need to do some date math. It can get a bit tricky avoiding arithmetic overflows and keeping things accurate:
A .Net tick is a duration of time lasting 0.1 microseconds. When you look at the Tick property of DateTime, you’ll see that it represents the number of ticks since January 1st 0001.
It's unlikely you'll be able to get the same kind of accuracy out of SQL as DateTime.Ticks since SQL doesn't express time in that much precision. SQL Server only stores time to approximately 1/300th of a second, whereas a single tick represents one hundred nanoseconds or one ten-millionth of a second.
SELECT DATEDIFF(s, '19700101', GETDATE())
The above query will get you a Unix-style timestamp if that works, but to make any real suggestions I'd need to know what you're using the data for. If you need higher resolution than 3 ms though, you need to look outside of SQL.
DATETIME: 0.01 (3 ms)
QueryPerformanceCounter: 0.0000001 (100 ns)
DATETIME2: 0.0000001 (7 decimal places, 100 ns)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
