date functions

DATEDIFF() vs DATEDIFF_BIG() in SQL Server: What’s the Difference?

by

If you’ve ever needed to find the difference between two dates in SQL Server, you might have used the DATEDIFF() function. This function returns the amount of time between two dates using a datepart specified by you. For example, you could use it to return the number of days between date 1 and date 2. You can also get it to return the number of minutes, seconds, months, years, etc.

The DATEDIFF_BIG() function works exactly the same way, but with one subtle difference: Its return data type.

So the difference between these two functions is the data type of their return value.

  • DATEDIFF() returns a signed integer (int)
  • DATEDIFF_BIG() returns a signed big integer (bigint)

Read more

What is the Year 2038 Problem?

by

The Year 2038 problem (also referred to as the Y2K38 bug) refers to a problem that some computer systems might encounter when dealing with times past 2038-01-19 03:14:07.

Many computer systems, such as Unix and Unix-based systems, don’t calculate time using the Gregorian calendar. They calculate time as the number of seconds since 1 January 1970. Therefore, in these systems, time is represented as a big number (i.e. the number of seconds passed since 1970-01-01 00:00:00). This is typically referred to as Epoch time, Unix time, Unix Epoch time, or POSIX time. As I write this, Unix time is 1560913841. And as I write this next line, Unix time has incremented to 1560913879.

Read more

How to Return the Unix Timestamp in SQL Server (T-SQL)

by

You might have noticed that SQL Server doesn’t have an equivalent of MySQL‘s UNIX_TIMESTAMP() function.

However, it’s not that difficult to return the Unix timestamp in SQL Server.

The 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.

Read more

YEARWEEK() Examples – MySQL

by

In MySQL, the YEARWEEK() function returns the year and week for a given date. You provide the date as an argument, and the function will return the result accordingly.

You also have the option of specifying whether to start the week on Sunday or Monday, and whether the week should be in the range 0 to 53 or 1 to 53.

Read more

WEEK() Examples – MySQL

by

In MySQL, you can use the WEEK() function to get the week number for a given date. By “week number” I mean the week of the year.

To use the function, simply provide the date as an argument and the week number will be returned.

You also have the option of specifying whether to start the week on Sunday or Monday, and whether the week should be in the range 0 to 53 or 1 to 53.

Read more

UTC_TIMESTAMP() Examples – MySQL

by

In MySQL, you can use the UTC_TIMESTAMP function to return the UTC date and time. UTC stands for Coordinated Universal Time and it’s the primary time standard by which the world regulates clocks and time.

The result of this function is returned either in ‘YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS format, depending on whether it’s used in a string or numeric context.

Read more

UTC_TIME Examples – MySQL

by

In MySQL, you can use the UTC_TIME function to return the UTC time. UTC stands for Coordinated Universal Time and it’s the primary time standard by which the world regulates clocks and time.

The result is returned either in ‘HH:MM:SS’ or HHMMSS format, depending on whether the function is used in a string or numeric context.

Read more

UTC_DATE Examples – MySQL

by

In MySQL, you can use the UTC_DATE function to return the UTC date. UTC stands for Coordinated Universal Time and it’s the primary time standard by which the world regulates clocks and time.

The result is returned either in ‘YYYY-MM-DD’ or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

Read more