Vikram Lakhotia Blog on Asp.Net, Dot Net, SQL, VIsual Studio, C# etc

Hi,

Last few days I have been working a lot in SQL server with DateTime data type and learned some simple but good tricks. One of the things that I learned was the month and year function. Normally when we have to find the month or year from a date variable or column we use the datepart function where we also need to pass what date part we need.

The month and year function do nothing special. They also return the month and year for the given date function. So what’s good about it? For me it only makes the query look easier to read and understand.

What do you do when you need the data for the latest month-year and we are storing month and year in the database instead of storing the date? I know we can concatnate the two columns with the day as 1 and get the date and compare them. Another easy way out can be to multiply the year with 12 and adding the month column to it. This will not have the over head of creating a date inside a query and easy to maintain. Here is an example. In the example table I have two column year and month.

where cast(intmonth as varchar) + '-1-' + cast(intyear as varchar)) = DateTime

Or

Where (intYear * 12) + intMonth  = ‘a variable calculated value for (intYear * 12) + intMonth’

The first one can be very complex look at and work with. With the second one I am able to make the same search and this will be faster as the query need not make a complex comparison between date time but can do with a simple calculation

Thanks
Vikram