I was doing some conversion of Oracle code (PL/SQL) to SQL Server code (T-SQL) – which had some quirks.
I just thought that I’d share a quick tip that has helped me out quite a bit.
Much of the Oracle code had time manipulation functions – but they were done in a way that I didn’t find intuitive. There was a lot of what appeared to be casting (or similar) between text stings containing dates and time.
One of the nice things about T-SQL – in my opinion – is that there are a lot of different data types, some of which deal with times.
On top of that there are functions that allow a specific part of a date/time to be captured – for example we might want the month number, of the day number of the month or the hour, or any part of a date / time really.
So, here’s a list of some of the ways to get that information directly from SQL Server.
SELECT DATEPART(DAY , SYSDATETIME());
SELECT DATEPART(YEAR , SYSDATETIME());
SELECT DATEPART(QUARTER , SYSDATETIME());
SELECT DATEPART(MONTH , SYSDATETIME());
SELECT DATEPART(DAYOFYEAR , SYSDATETIME());
SELECT DATEPART(DAY , SYSDATETIME());
SELECT DATEPART(WEEK , SYSDATETIME());
SELECT DATEPART(WEEKDAY , SYSDATETIME());
SELECT DATEPART(HOUR , SYSDATETIME());
SELECT DATEPART(MINUTE , SYSDATETIME());
SELECT DATEPART(SECOND , SYSDATETIME());
SELECT DATEPART(MILLISECOND , SYSDATETIME());
SELECT DATEPART(MICROSECOND , SYSDATETIME());
SELECT DATEPART(NANOSECOND , SYSDATETIME());
SELECT DATEPART(TZOFFSET , SYSDATETIME());
SELECT DATEPART(ISO_WEEK , SYSDATETIME());
You can read more at the following link
Have a great day
Cheers
Martin.