In SQL Server, the SWITCHOFFSET() function can be used to return a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.
Below are examples of how this function works.
Tag: dates
How to Find the Last Day of the Month in SQL Server
Starting with SQL Server 2012, the EOMONTH() function allows you to find the last day of any given month. It accepts two arguments; one for the start date, and one optional argument to specify how many months to add to that date.
This article provides examples that demonstrate how EOMONTH() works in SQL Server.
DATEADD() Examples in SQL Server
In SQL Server, you can use the DATEADD() function to add a specified time period to a given date. You can also use it to subtract a specified time period.
You can also combine DATEADD() with other functions to format the date as required. For example, you could take ‘2020-10-03’, add 10 years, then return the (increased) year component.
This article contains examples to demonstrate.
Continue readingDATEDIFF() Examples in SQL Server
In SQL Server, you can use the T-SQL DATEDIFF() function to return the difference between two dates/times. It works on any expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value.
This article provides examples of the DATEDIFF() function in SQL Server.
DATEDIFF_BIG() Examples in SQL Server
In SQL Server, you can use the DATEDIFF_BIG() function instead of the DATEDIFF() function if you expect the returned value to be really big. For example, if you’re trying to find out how many milliseconds are in a 1000 years, you’ll get an error.
That’s because DATEDIFF() returns an int data type, and the result is too big for that data type to handle. On the other hand, the DATEDIFF_BIG() function returns a signed bigint data type, which means you can use it to return much larger values. In other words, you can use with a much larger range of dates.
Other than that, there’s not really any difference between the two functions.
The article provides examples of using the DATEDIFF_BIG() function in SQL Server.
6 Functions to Get the Day, Month, and Year from a Date in SQL Server
Transact-SQL includes a bunch of functions that help us work with dates and times. One of the more common tasks when working with dates is to extract the different parts of the date. For example, sometimes we only want the year, or the month. Other times we might want the day of the week. Either way, there are plenty of ways to do this in SQL Server.
In particular, the following functions allow you to return the day, month, and year from a date in SQL Server.
These functions are explained below.
SQL Server DATEPART() vs DATENAME() – What’s the Difference?
When working with dates in SQL Server, sometimes you might find yourself reaching for the DATEPART() function, only to realise that what you really need is the DATENAME() function. Then there may be other situations where DATEPART() is actually preferable to DATENAME().
So what’s the difference between the DATEPART() and DATENAME() functions?
Let’s find out.
DATENAME() Examples in SQL Server
In SQL Server, the T-SQL DATENAME() function returns a character string that represents the specified datepart of the specified date. For example, you can pass in 2021-01-07 and have SQL Server return only the month portion (January).
The return type for DATENAME() is nvarchar.
Examples below.
DATEPART() Examples in SQL Server
In SQL Server, the T-SQL DATEPART() function returns an integer that represents the specified datepart of the specified date. For example, you can pass in 2021-01-07 and have SQL Server return only the year portion (2021).
You can also extract the time portion. For example, you can pass in 2018-06-02 08:24:14.3112042 and have SQL Server return only the hour portion (8).
Examples below.
Continue reading5 Ways to Get the Short Month Name from a Date in SQL Server
Sometimes when working with SQL Server (or any other DBMS for that matter), you need to return the short name for a month. By this I mean the 3 letter abbreviation of a month. For example, you need “Dec” instead of “December”.
Here are four ways you can extract the shortened month name from a date in SQL Server.