Wednesday, August 5, 2009

Formatting a Date Time

Today i saw a thread in msdn asking about how to format datetime in his required format. The poster sited the ease of doing this in MySQL. It prompted me to write a simple datetime formatting function. Here it is.

IF Object_id('dbo.fn_format_date') IS NOT NULL
    DROP FUNCTION dbo.fn_format_date


CREATE FUNCTION dbo.Fn_format_date
               (@datetime DATETIME,
                @format   VARCHAR(100))
    DECLARE  @token  TABLE(
                           id    INT    IDENTITY,
                           token VARCHAR(10), 
                           VALUE VARCHAR(10)
    INSERT INTO @token
    -- Month in string
    SELECT '%mth', Datename(MONTH,@datetime)             UNION ALL  -- Month full word
    SELECT '%mon', Left(Datename(MONTH,@datetime),3)     UNION ALL  -- Month abrv
    SELECT '%wday', Datename(weekday,@datetime)          UNION ALL  -- weekday
    SELECT '%wd', Left(Datename(weekday,@datetime),3)    UNION ALL  -- weekday abrv
    -- 4 digits year, 2 digits month, day etc
    SELECT '%yyyy', Convert(VARCHAR(4),Datepart(YEAR,@datetime)) UNION ALL  -- Year
    SELECT '%mm', Right('00' + Convert(VARCHAR(2),Datepart(MONTH,@datetime)),
                 2)   UNION ALL  -- Month
    SELECT '%dd', Right('00' + Convert(VARCHAR(2),Datepart(DAY,@datetime)),
                 2)   UNION ALL  -- Day
    SELECT '%hh', Right('00' + Convert(VARCHAR(2),Datepart(HOUR,@datetime)),
                 2)   UNION ALL  -- Hour 
    SELECT '%mi', Right('00' + Convert(VARCHAR(2),Datepart(MINUTE,@datetime)),
                 2)   UNION ALL  -- Minute
    SELECT '%ss', Right('00' + Convert(VARCHAR(2),Datepart(SECOND,@datetime)),
                 2)   UNION ALL  -- Second
    SELECT '%ms', Right('000' + Convert(VARCHAR(2),Datepart(SECOND,@datetime)),
                 3)   UNION ALL  -- Millisecond
    -- 2 digits year
    SELECT '%yy', Right(Convert(VARCHAR(4),Datepart(YEAR,@datetime)),
                 2)   UNION ALL  -- Year
    SELECT '%y', Right(Convert(VARCHAR(4),Datepart(YEAR,@datetime)),
                 1)   UNION ALL  -- Year
    SELECT '%m', Convert(VARCHAR(2),Datepart(MONTH,@datetime))  UNION ALL
    SELECT '%d', Convert(VARCHAR(2),Datepart(DAY,@datetime))    UNION ALL
    SELECT '%h', Convert(VARCHAR(2),Datepart(HOUR,@datetime))   UNION ALL
    SELECT '%i', Convert(VARCHAR(2),Datepart(MINUTE,@datetime))  UNION ALL
    SELECT '%s', Convert(VARCHAR(2),Datepart(SECOND,@datetime))
    SELECT   @format = Replace(@format,token,VALUE)
    FROM     @token
    ORDER BY id
    RETURN @format


-- Testing
SELECT formatted_datetime = dbo.Fn_format_date(Getdate(),fmt)
FROM   (

        SELECT fmt = '%yyyy.%mm.%dd %hh:%mi:%ss.%ms (%wd)'
        UNION ALL
        SELECT fmt = '%dd %mon %yy %hh:%mi:%ss.%ms (%wday)'

       ) f

2009.08.05 19:07:05.005 (Wed)
05 Aug 09 19:07:05.005 (Wednesday)

Of-course datetime formatting should still be performed at the front end application or reporting tool where the data is being displayed.

Monday, August 3, 2009

Find the next business days


Calculate the next x business day (excluding Sat, Sun). Basically a businessdateadd() for dateadd()

IF     EXISTS (SELECT * FROM sysobjects WHERE xtype = 'FN' AND name = 'fn_next_business_day')
DROP FUNCTION fn_next_business_day

CREATE FUNCTION fn_next_business_day
@start_date datetime,
@days int
RETURNS datetime
DECLARE @wd int

-- get the weekday AND CONVERT to datefirst = 1 value
SELECT @wd = ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1

-- IF it IS Sat, Sun, change teh date to Next Monday
IF @wd IN (6, 7)
SELECT @start_date = DATEADD(DAY, 7 - @wd + 1, @start_date)
SELECT @wd = ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1

@days + CASE WHEN @days >= (5 - @wd + 1)
THEN ((@days + ((DATEPART(weekday, @start_date) - 1 + 7 - (8 - @@datefirst)) % 7) + 1 - 1)/ 5) * 2

Tuesday, July 28, 2009

Find Beginning and Ending of Period

We all know how to find the first and last day of a year. The first day is always January 1 and the last day of the year is December 31. How about first and last day of a month ? or maybe week or maybe quarter.

There are lots of way of doing this, but this is my preferred way.

To find the beginning and ending of current year

select    begin_of_curr_year    = dateadd(year, datediff(year, 0, getdate()), 0),
end_of_curr_year = dateadd(year, datediff(year, 0, getdate()) + 1, -1)
 What about a specific year ?
declare    @year    int
select @year = 2010
select begin_of_selected_year = dateadd(year, @year - 1900, 0),
end_of_selected_year = dateadd(year, @year - 1899, -1)
And put it as a function
create function dbo.fn_begin_end_of_period
@type int, -- 1 : year, 2 : quarter, 3 : month, 4 : week
@year int,
@period int -- ignore for year
returns table
select begin_of_period = case @type
when 1 then dateadd(year, @year - 1900, 0)
when 2 then dateadd(quarter, @period - 1, dateadd(year, @year - 1900, 0))
when 3 then dateadd(month, @period - 1, dateadd(year, @year - 1900, 0))
when 4 then dateadd(week, @period - 1, dateadd(year, @year - 1900, 0))
end_of_period = case @type
when 1 then dateadd(year, @year - 1899, -1)
when 2 then dateadd(quarter, @period, dateadd(year, @year - 1900, -1))
when 3 then dateadd(month, @period, dateadd(year, @year - 1900, -1))
when 4 then dateadd(week, @period, dateadd(year, @year - 1900, -1))

Monday, July 13, 2009

No of Weekdays in a year

I posted this over at MSDN T-SQL forum

How to find number of weekdays in a year.

1 year = 365 or 366 days = 52 week
1 week = 5 week days

so minimum there will be 52 x 5 week days in a year. So basically only need to determine the 365th day or 366th day of the year is it a week day. This is handle by the 2 case statements in the query

declare @year int,
@boyear datetime,
@eoyear datetime

select @year = 2009
select @boyear = dateadd(year, @year - 1900, 0),
@eoyear = dateadd(year, @year - 1900 + 1, -1)

select weekdays_year = (52 * 5)
+ case when dateadd(day, (52 * 7), @boyear) <> @eoyear and datename(weekday, dateadd(day, (52 * 7), @boyear)) not in ('Saturday', 'Sunday')
then 1
else 0
+ case when datename(weekday, @eoyear) not in ('Saturday', 'Sunday')
then 1
else 0

Thursday, June 11, 2009

Calculating Business Day without Calendar table

If you don’t have a calendar table, not a problem. You can still able to calculate Business Day easily by making use of a function table F_TABLE_DATE written by MVJ.

Let me show you the code

select    count(*) - 1
from F_TABLE_DATE('2009-06-04', '2009-06-09')
where WEEKDAY_NAME not in ('Sat', 'Sun')
And you will get the same result 3 business days as before.
But that does not exclude any holiday. if you have a table that stores the dates of the holiday, something like

create table holiday
holiday_date datetime
then you can make used of that and LEFT JOIN to it.

select    count(*) - 1
from F_TABLE_DATE('2009-06-04', '2009-06-09') d
left join holiday h on d.[DATE] = c.holiday_date
where WEEKDAY_NAME not in ('Sat', 'Sun')
and h.holiday_date is null
That’s not too bad isn’t it ?
Next, we will see how to calculate if you don’t have a calendar table and does not want to make use of a Function table like F_TABLE_DATE.

Monday, June 8, 2009

Calculate Business Day with Calendar table

Yesterday I posted about Business Day. Now let's look into more details on how to do that.
For the first method, which is the most ideal case is you have a calendar table to store all the dates. The calendar table will looks something like

create table calendar
dates datetime,
week_day varchar(3)

Dates is a datatime field containing the date only. Week_day is the name of the weekday like 'Mon', 'Tue', 'Wed' etc. The week_day is there to simplify and to give better performance to your query. If you don't have the week_day column in there you still can get the week_day using datename(weekday, dates) but this will result in bad performance of the query.

Now let's put some sample data into the calendar table

insert into calendar (dates, week_day)
select '2009-06-04', 'Thu' union all
select '2009-06-05', 'Fri' union all
select '2009-06-06', 'Sat' union all
select '2009-06-07', 'Sun' union all
select '2009-06-08', 'Mon' union all
select '2009-06-09', 'Tue'

Next, let's see how to calculate business day with a calendar table

select count(*) - 1
from calendar
where dates >= '2009-06-04'
and dates <= '2009-06-09'
and week_day not in ('Sat', 'Sun')

And the query will give you 3 as a result.

Now what if, you also want to exclude holiday. Surely holiday does not counted as a business day right ?.

That's easy. Just add a column to indicate the holiday.

create table calendar
dates datetime,
week_day varchar(3),
holiday bit

And let's include one of the days as holiday

insert into calendar (dates, week_day)
select '2009-06-04', 'Thu', 0 union all
select '2009-06-05', 'Fri', 0 union all
select '2009-06-06', 'Sat', 0 union all
select '2009-06-07', 'Sun', 0 union all
select '2009-06-08', 'Mon', 1 union all
select '2009-06-09', 'Tue', 0

And the new query will be

select count(*) - 1
from calendar
where dates >= '2009-06-04'
and dates <= '2009-06-09'
and week_day not in ('Sat', 'Sun')
and holiday = 0

And the result will be simply 2.

Next I will look at how to business day if you don't have a calendar table

Sunday, June 7, 2009

Business Day

What is Business Day ? Wikipedia define it as "A business day is any day including Monday to Friday and does not include holidays".

How many business day is between 2 dates ? like between June 4th 2009 and June 9th 2009.

In SQL Server, the number of days between 2 days can be calculated using datediff() function

SELECT days = DATEDIFF(DAY, '2009-06-04', '2009-06-09')

But this will give you 5 days.




The business days between June 4th 2009 and June 9th 2009 should be 3 excluding Saturday and Sunday.

So how to calculate the number of business days between 2 dates ?

I will go though various methods in my coming posts

Area to cover will be