You will love this blog because date functions and formatting differ dramatically between databases, but I have covered that with brilliant examples and clear and concise explanations below.

All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the world. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website.

https://coffingdw.com/education/

Below the video are great date functions on SQL Server. However, you can first witness a two-minute video that joins a Synapse table with tables from Databricks, Snowflake, Redshift, Google BigQuery, Vertica, Teradata, Oracle, SQL Server, DB2, Yellowbrick, MySQL, Postgres, Microsoft Access, Excel in a single federated query.

The Getdate command will return today’s date and time, just like the Current_Timestamp command. However, the Getdate function is not ANSI standard.

The DATEADD function adds or subtracts an interval from a date. Valid values for the part argument include the year, quarter, Month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond, TZoffset, and ISO_WEEK.

The Dateadd command adds a specified time interval to a date or timestamp value. Below we use examples of DAY, WEEK, MONTH, and QUARTER.

You can also use the DATEADD to add hours, minutes and seconds.

Below are some excellent examples to pull from using the DATEPART function.

The YEAR, MONTH, and DAY functions are abbreviations for the DATEPART function. 

The DATENAME function returns the name of the requested part rather than the number. Notice above that only the Month returns the actual name of the Month, but both the Year and the Day still return the integer values.

The syntax for the DATEDIFF function is DATEDIFF (part, dt_val1, dt_val2).  Below, we are using literal dates, but you can use column values.  We then can see the differences in the number of years, months, days, hours, minutes, and seconds.

The FORMAT  function returns a value formatted with the specified format and optional culture. For example, as we have done for the United States and Great Britain, you can use the FORMAT function for locale-aware formatting of date/time and number values as strings.

The FORMAT  function returns a value formatted with the specified format and optional culture. For example, as we have done for Germany and China, you can use the FORMAT function for locale-aware formatting of date/time and number values as strings.

The FORMAT  function returns a value formatted with the specified format and optional culture. For example, you can use the FORMAT function for locale-aware formatting of date/time and number values as strings.

You can also format time as in the example below.

The DATEFROMPARTS function returns a date value, with the date portion set to the specified year, month, and day and the time portion set to the default. For invalid arguments, DATEFROMPARTS kicks out an error. DATEFROMPARTS returns null if at least one required argument has a null value.

GETDATE(): Returns the current local date and time.
CURRENT_TIMESTAMP: Returns the current local date and time, following ANSI SQL standards.
GETUTCDATE(): Returns the current UTC date and time.

SYSDATETIME(): This function returns the current system date and time with high precision, including fractional seconds. It is similar to GETDATE() but provides a higher level of precision. The value returned is in the SQL Server’s local time zone.

SYSUTCDATETIME(): This function also returns the current system date and time with high precision, including fractional seconds, but in Coordinated Universal Time (UTC). Unlike SYSDATETIME() or GETDATE(), which return local time, SYSUTCDATETIME() ensures that the returned time is in UTC, which is useful for maintaining consistency across different systems or time zones.

SYSUTCDATETIME(): Returns the current UTC date and time with high precision.
SYSDATETIMEOFFSET(): Returns the current local date and time with the corresponding time zone offset from UTC.

EOMONTH finds the end of the month for a given date. It’s useful for various reporting and analysis tasks where you need to reference the end of a month. The function takes two parameters: the input date and an optional parameter indicating the number of months to add or subtract from the input date before calculating the end of the month. If the second parameter is omitted or set to 0, it returns the end of the current month. If it’s positive, it calculates the end of the month for a future date, and if it’s negative, it calculates the end of the month for a past date.

Posted in Blog