Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot christmas Logo

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Browsing "dates"

1

Displaying missing dates by utilizing a calendar table

We got a question today from a user who wanted to display counts of zero where dates were missing. For example if you had the following data

OrderDate SomeCount
2013-01-01 2
2013-01-02 2
2013-01-03 1
2013-01-05 2
2013-01-07 1
2013-01-08 1
2013…

Read More...
4

SQL Advent 2011 Day 1: Date and time

In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code tha…

Read More...
5

DATEFROMPARTS and DATETIMEFROMPARTS functions in SQL Server Denali CTP3

Function

Syntax

Return value

Return data type

DATEFROMPARTS…

Read More...
5

A Quick look at the new EOMONTH function in SQL Server Denali CTP3

SQL Server Denali CTP3 has a bunch of new date/time functions like DATEFROMPARTS, DATETIMEFROMPARTS and EOMONTH

First let’s take a look at EOMONTH.

The syntax for EOMONTH is

EOMONTH ( start_date [, month_to_add ] )

If you pass in getdate()…

Read More...
6

When changing column data types use ALTER TABLE TableName ALTER Column syntax, don’t drop and recreate column

Someone asked how to change the data type from datetime to datetime2

Someone else answered the following

You could add the new column.

UPDATE Table SET NewColumn = OldColumn

delete the old column

then rename the new column.

This of cou…

Read More...
2

Searching for ranges when you have quarters and years

Someone posted a question, they wanted to return the quarters and years within a range that were passed in. The problem they had is that they stored this data in a year and a quarter column. The table looked like this CREATE TABLE Periods(PeriodQuarter INT,PeriodYear INT) INSERT Periods VALUES (1,2009) INSERT Periods VALUES (2,2009) INSERT […]

Read More...
4

Operand type clash: date is incompatible with int error when trying to do +1 on a date data type in SQL Server 2008

I have seen enough questions about this lately and this means that it is time for a blogpost. SQL Server 2008 has a bunch of new data types and one of them is the date datatype. If you don’t care for the time portion of the date you can now use the date data type […]

Read More...
2

Adding time offsets passed in to a datetime to generate localized datetime

I answered this question today and thought it would be useful to create a little post If you have a varchar value like this ’2009-06-26 14:30:00.000Z+4:30′ you want to take 4 hours and 30 minutes and then subtract that from the date itself so in this case you will get 2009-06-26 11:00:00.000. The reason you […]

Read More...
4

Grouping datetime values in half hour blocks in SQL Server

I answered this question a while back and decided to create a little blogpost. Let’s say you have the following datetime values 2009-05-12 11:13:19.667 2009-05-12 11:12:19.667 2009-05-12 11:33:19.667 2009-05-12 11:43:19.667 2009-05-12 11:03:19.667 2009-05-12 11:53:19.667 2009-05-12 11:53:19.667 2009-05-12 11:23:19.667 2009-05-12 12:13:19.667 2009-05-12 12:12:19.667 2009-05-12 13:33:19.667 2009-05-12 13:43:19.667 2009-05-12 14:03:19.667 2009-05-12 14:53:19.667 2009-05-12 15:53:19.667 2009-05-12 15:23:19.667 What […]

Read More...
11

ISO Week In SQL Server

ISO Week in SQL Server First let’s take a look at what ISO week is, from WikiPedia: Week date representations are in the format as shown below. YYYY-Www or YYYYWww YYYY-Www-D or YYYYWwwD [YYYY] indicates the so-called ISO year which is slightly different than the calendar year (see below). [Www] is the week number prefixed […]

Read More...
Next Page »