A few days ago Adam Machanic proposed a great idea about a T-SQL Tuesday. Every Tuesday there would be a topic bloggers all around would post about. Chosen as the first topic was the date and time stuff in SQL Server.
Because there’s already all this great content out there I’m not going to repeat it but I’m going to put together a ton of datetime resources from SQLTeam forums and blogs as a resource you can use in the future.
MVJ as we like to call him in the forums has a thing for datetime manipulation. He has some really good helper functions he shared with the world.
- Date Table Function F_TABLE_DATE – the amazing Calendar table that is very useful.
- Convert YYYYMM Integer Date to Datetime
- Age Function F_AGE_IN_YEARS
- ISO Week of Year Function
- Date/Time Info and Script Links - the ultimate list of all things DateTime
Peter is an MVP who has the most amazing ideas for writing SQL. If you want performance talk to him.
- Get the Nth weekday of any arbitrary period
- How to calculate the number of weekdays in a month
- How to get the Weekday and Nth from a date
- How to calculate the number of weekdays for any given period
- How to calculate number of weekdays in a year
Sometimes I think that Jeff is datetime machine. His posts are always high quality and simple to understand. He’s also an MVP who I hope will start blogging more again soon.
- Working with Time Spans and Durations in SQL Server
- Group by Month (and other time periods)
- Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME
- Data Types - The Easiest Part of Database Design
- How to format a Date or DateTime in SQL Server
- Breaking apart the DateTime datatype -- Separating Dates from Times in your Tables
- Date Only and Time Only data types in SQL Server 2005 (without the CLR)
- Essential SQL Server Date, Time and DateTime Functions
K H Tan is a smart fellow from Singapore who’s always glad to help. Shame he doesn’t blog.
- fn_next_business_day - Calculates the next x business day (excluding Sat, Sun). Basically a businessdateadd() for dateadd()
In each post I learned something new or got a nice idea so it’s also worth reading the comments. On some issues my views have changed while on others they stayed the same.
- Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT
- How to store an incomplete date?
- SQL Server: The one and only locale insensitive date format
- I don't like having Nulls in DateTime columns