SQL DATES

Dates to me are one of the most annoying items within SQL. Getting dates, performing date validation checks, return sets based on dates, converting dates, transforming dates. I find this can be a real pain at times.

Every SQL dba knows the
Select getdate() - will return the current system date.

Select getdate()-1 will return the current date - 1 day

Select getdate()+1 will return the current date + 1 day
these are pretty simple.

for the most part dates are simple to work with, but they are just annoying when you need to convert them. My biggest peeve is working in a database that has columns that are datetime columns and other columns that are a different datatype but are date columns like Int for example. Or someone has created a date column as a char(8) or something of that nature. I find it frustrating when the date columns take on multiple data types. Even in circumstances where they could of been consistent.

select getdate() AS CurrentDate,
'Q'+DATENAME(quarter, convert(char, getdate())) As Quarter,
DATENAME(year, convert(char, getdate())) As Year,
DATENAME(month, convert(char, getdate())) as Month,
DATENAME(Dayofyear, convert(char, getdate())) as DayofYear,
DATENAME(day, convert(char, getdate())) as Day,
DATENAME(week, convert(char, getdate())) as Week,
DATENAME(WEEK, GETDATE()) AS CurrentWeek,
DATENAME(MONTH, GETDATE()) AS CurrentMonth

SELECT convert(varchar,DATEADD(mm, DATEDIFF(mm,0,getdate())-25, 0),112) as [First Day of Previous Month 24 months ago]

more to come....