SQL UK Date Format & SQL WorkDays

[PWD]

Good Afternoon. I have two issues but I think that a solution to the first may do. I found some code to create a table I can (try to) use to identify work days when submitting an electronic holiday form.

Now I'm not saying that this is good code but it was the only starting place I could find. The main issue is trying to get the date in UK dd/mm/yyyy format

The first part was to create a table of numbers.

 -- Create the ‘Numbers’ table first
CREATE TABLE dbo.Numbers 
( 
    Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED 
) 
 
WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 11024 
BEGIN 
    INSERT dbo.Numbers DEFAULT VALUES 
END

Then it was time to create the Calendar Table. Is it possible/should I be able to format the date at this stage?

 CREATE TABLE dbo.Calendar  
(  
    
Date SMALLDATETIME NOT NULL 
        PRIMARY KEY CLUSTERED,  
    
    isWeekday BIT, 
    isHoliday BIT,  
    Year SMALLINT,  
    FinancialYear SMALLINT,  
    Quarter TINYINT,  
    Month TINYINT,  
    Day TINYINT,  
    DayOfWeek TINYINT, 
    MonthName VARCHAR(9), 
    DayName VARCHAR(9), 
    Week TINYINT 
) 
GO

I wondered whether I could format when I do this bit as the

 SET DATEFORMAT dd/mm/yyyy

doesn't work.

 INSERT Calendar(Date) 

    SELECT DATEADD(DAY, Number, '20100100') 
        FROM dbo.Numbers 
        WHERE Number <= 10957 
-- 01/01/2040
SET DATEFORMAT dd/mm/yyyy
        ORDER BY Number

If I can get that bit right then the following could be called from our form

 --Number of Business days in Holiday selection
SELECT COUNT(date) AS 'Holidays' FROM dbo.Calendar 
    WHERE isWeekday = 1 
    AND isHoliday = 0 
    AND date >= '20100401' AND date <= '20100408'

But as UK date format

 AND date >= '01042010' AND date <= '08042010'

Am I on the right track, or completely bonkers?

Many thanks.

Des

 

[mrdenny]

You should be correct. Changing the DATEFORMAT setting will change the date in your session. If you set your language to English (UK) then it should set it to the UK date format all the time.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[gmmastros]

Let me try explaining a couple things.

When you use the DateTime or SmallDateTime data type there is no concept of formatting. Internally, DateTimes are stored as a pair of integers.

Formatting only affects how you see a DateTime or when converting to and from strings (char, varchar, etc...).

AND date >= '01042010' AND date <= '08042010'

This will not work. Since there are multiple date formats around the world, you must specify the date format or use one of the 2 non-ambiguous date formats that SQL Server recognizes.

The 2 non-ambiguous date formats are:

YYYYMMDD HH:MM:SS.mmm
YYYY-MM-DD[!]T[/!]HH:MM:SS.mmm

In your example, you do not use any separators, so SQL Server will interpret your string of 8 numbers as YYYYMMDD, and you will likely get an "out of range" exception.

If you set the language for the user logging in to the database as English (UK) AND you use a date separator, it will be interpreted by SQL server as dd/mm/yyyy. Alternatively, you can use the SET DATEFORMAT dmy command which will cause SQL Server to interpret your date as dd/mm/yyyy. Again... if you do not use a date separator, SQL will ALWAYS interpret your data as YYYYMMDD.

For more reading on this:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/setting-a-standard-dateformat-for-sql-se

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWD]

OK let's go back a step then. How would it be possible to create a column in a table with dd/mm/yyyy format for the next 20 years?

 

[PWD]

I can see that

 Select Convert(VarChar(10), dt, 103)
FROM dbo.Calendar

will display what I want. Can I get it to write this back over the original data?

Des.

 

[gmmastros]

Using your existing stuff (but tweaking slightly)....


The following will insert data in to your calendar table. Note that the Date column is still DateTime.

INSERT Calendar(Date)

    SELECT DATEADD(DAY, Number-1, '20100101')
        FROM dbo.Numbers
        WHERE Number <= 10957

Now, add a UKDate column.

Alter Table Calendar Add UKDate VarChar(10)

Now, fill it with data.

Update Calendar 
Set    UKDate = Convert(VarChar(10), Date, 103)

Now, make sure it's there and looks ok.

Select * from Calendar

I encourage you to keep the Date column as a DateTime data type. I am convinced that you will find many uses for this. Adding another column for UKDate isn't necessarily a bad idea, and if that's what you want to do, then do it.

You should be made aware that filtering on the UKDate column will probably NOT do what you want. When filtering date ranges, the filter will behave like a string compare, not a date compare like you probably want.

For example, you probably think that the following query would return just 2 rows.

Select * 
From   Calendar
Where  UKDate Between '05/03/2010' and '06/03/2010'

In fact, you get 361 rows. This happens because UKDate, and the between values are all strings, so SQL Server does a string comparison to determine what is included in the result set.

If you use the Date column and format your dates UK style (and set the DateFormat), you will get the results you expect.

Set DateFormat DMY

Select * 
From   Calendar
Where  Date Between '05/03/2010' and '06/03/2010'

Now you get the 2 rows like you would expect.

If I can get that bit right then the following could be called from our form


--Number of Business days in Holiday selection
SELECT COUNT(date) AS 'Holidays' FROM dbo.Calendar
WHERE isWeekday = 1
AND isHoliday = 0
AND date >= '20100401' AND date <= '20100408'

But as UK date format

AND date >= '01042010' AND date <= '08042010'

That query will NEVER work. Not with the date filter that you WANT to use. In fact, the preferred method is with the first example (AND date >= '20100401' AND date <= '20100408'). Is there a compelling reason that you want to use the second version instead of the first? What I am suggesting is.... use the first format YYYYMMDD. Allow the user to enter dates in UK format, but then manipulate it a bit with your front and language to put the dates in YYYYMMDD format.

If you try to do anything else, I suspect you will eventually end up with a "bonkers" system that is hard to maintain.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWD]

Hi George. Many thanks. Dang, it seemed as if was all going very well - putting in the UKDate column. Unfortunately it goes wrong (as you predicted) when I tried to use

 AND UKDate >= '01/04/2010' AND UKDate <= '08/04/2010'

My colleague has created a holiday form using a program called 'Workflow Director' and, being in the UK, it picks up dates in dd/mm/yyy format. I'll have a chat with him to see if it can be manipulated to be the yyyymmdd format.

I'll post again next week.

Des.