Datetime/public holidays

I need to be able to determine whether a particular
datetime value is a public holiday (in the uk) or not and
I cannot find a documented way of deteriming this. Please
could you advise me whether there is a function or method
of determining this in SQL 2000 Enterprise Edition?
If there is not could you advise me a robust method of
providing this functionality for developers (I am a design
DBA who works with a number of different development teams
and it would seem appropriate for everyone to use the same
method)?
Many thanks,
DavePublic holiday dates are not always determined by a fixed logic. You should
build your own calendar table for this and populate it in advance with as
much data as you need.
CREATE TABLE Calendar (caldate DATETIME NOT NULL PRIMARY KEY, workingday
CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')
INSERT INTO Calendar (caldate) VALUES ('20000101')
Populate it (this is 11 years worth of dates):
WHILE (SELECT MAX(caldate) FROM Calendar)<'20101231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Now update the non-working days:
UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')
A source I use for public holiday dates is: http://www.bank-holidays.com
--
David Portas
--
Please reply only to the newsgroup
--

DateTime Ranges

Hi..
I am facing a problem trying to determine whether a point in tie falls within a specific date and time range.
Here is an example..
Is 7/20/2007 1:23:45PM in the range between (Thursday 8:00 PM) To (Sunday 7:59 AM)
ThanksI've not got Crystal on this PC, so excuse any errors, but I'd expect you could do something like

numbervar d := dayofweek({date}); //or whatever the 'get day' function is!
timevar t := ctime({date}); //Get just the time part

//return whether between Thursday 8pm and Sunday 8pm
(d = CrThursday and t >= ctime(20, 0, 0))
or d = CrFriday
or d = CrSaturday
or (d = CrSunday and t < ctime(20, 0, 0))|||Thanks my friend,

I used your CRsyntax and converted it to Basic as follows:

Dim d As number
Dim t AS time

d= dayofweek(currentdatetime)
t= ctime(currentdatetime)

'return whether between Thursday 8pm and Sunday 8pm
IF (d = CrThursday and t >= ctime(20, 0, 0)) or d = CrFriday or d = CrSaturday or (d = CrSunday and t < ctime(8, 0, 0)) THEN
FORMULA= "Code if True"
Else
FORMULA= "Code if False"
END IF

DateTime Format in Localized version of MSDE

Hi
How do we determine the date time format in a SQL Server instance.
Specifically I would like to know, if the date time data type in SQL Server
is Language Specific or Language Neutral.
We are facing the following problem. I have a managed app, which is
localized. I need to update some data from the managed app to the
database(we are using MSDE). When I run the managed app in Italian locale,
with Italian build of MSDE, the database update fails.
The problem we figured out was, the date time cast in database fails. This
is because the time separator(for Italian locale) in .NET app is a period,
while in SQL MSDE(Italian build) it is a colon (
The following are my queries.
1. Is Date Time data type in SQL Language specific or Language Neutral? If
it is Language Neutral, I assume it will use the en-US culture, correct me
if I am wrong.
2. If date time is language specific, how is the collation set. Is it set by
default when MSDE is installed? Will the Operating System language version,
impact the collation, while installing MSDE.
3. When I run the query 'Select GetDate()' in Query Analyzer, the time
separator is displayed as a colon. Does the language version of SQL Server
tools(query analyzer/enterprise manager) have an impact on the date time
displayed?
Your inputs will help me a lot. Please reply to my ID (Ramjee_t@.infosys.com)
Thanks
RT
In message <ep1Idw#aFHA.580@.TK2MSFTNGP15.phx.gbl>, ramjee
<ramjee_t@.infosys.com> writes
>Hi
>How do we determine the date time format in a SQL Server instance.
>Specifically I would like to know, if the date time data type in SQL Server
>is Language Specific or Language Neutral.
>We are facing the following problem. I have a managed app, which is
>localized. I need to update some data from the managed app to the
>database(we are using MSDE). When I run the managed app in Italian locale,
>with Italian build of MSDE, the database update fails.
>The problem we figured out was, the date time cast in database fails. This
>is because the time separator(for Italian locale) in .NET app is a period,
>while in SQL MSDE(Italian build) it is a colon (
>The following are my queries.
>1. Is Date Time data type in SQL Language specific or Language Neutral? If
>it is Language Neutral, I assume it will use the en-US culture, correct me
>if I am wrong.
Not exactly. Physically in the database it is always stored the same way
however, the collation order does determine some of the supported
formats displaying and updating a DateTime field.

>2. If date time is language specific, how is the collation set. Is it set by
>default when MSDE is installed? Will the Operating System language version,
>impact the collation, while installing MSDE.
The default collation order is set when the instance of MSDE is
installed. However under MSDE 2000 / SQL Server 2000 the collation order
of each database can be different. Thats up to you when you CREATE the
DATABASE (ie: you determine the default collation order for each
database). In addtion, you can specify the collation order to use on
each Table and Field if really required. Check BOL for the CREATE
DATABASE and TABLE. You are therefore quite capable of using the same
collation order for every instance of MSDE you install regardless of
country.

>3. When I run the query 'Select GetDate()' in Query Analyzer, the time
>separator is displayed as a colon. Does the language version of SQL Server
>tools(query analyzer/enterprise manager) have an impact on the date time
>displayed?
Its all about handling dates in a consistent manor.
Its generally a good idea to always update a DataTime field using the
universal format "yyyy-mm-dd hh:nn:ss". By doing this, MSDE never gets
confused about which part is the month and day (ie: 2005-01-05 is always
5th Jan whereas 05-01-2005 could be 5th Jan or 1st May). Again, this
also solves international differences.
Its also therefore generally a good idea to always retrieve the DateTime
in a known format. Therefore using a command like "SELECT
Convert(datetime, MyDateField, 102) as MyDate FROM ..." would always
return the date in a UK format for example. That way your application
does not get confused and the localisation to the client is left to your
application.

>Your inputs will help me a lot. Please reply to my ID (Ramjee_t@.infosys.com)
No Problem.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||Hi Andrew,
You are mixing up collation, which is a property of character type columns
and variables in SQL Server, and the language that can be set for a
connection or user. The last one determines how dates as strings are
interpreted.
1) You are right that datetime and smalldatetime in SQL Server are stored in
a binary, language-neutral format. How the datetimes are displayed depends
on the client application however. For example Query Analyzer will by
default display dates in yyyy-mm-dd hh:mm:ss format. Enterprise Manager on
the other hand will use your Windows local settings to decide the display
format. How dates as strings are interpreted when inserting, updating or
deleting depends on the language setting for the connection, which are by
default derived from the language settings for the current user, although
they can be set explicitly with SET LANGUAGE.
2) As I said earlier, collation is irrelevant for datetime. The default
language settings for the user (login) are derived from the language in
which SQL Server is installed, but can be specified explicitly when creating
the login, or changed afterwards.
3) "yyyy-mm-dd hh:nn:ss" is not a safe format for datetime. Try the
following:
SET LANGUAGE us_english
SELECT CAST('2005-06-14 00:00:00' AS DATETIME)
GO
SET LANGUAGE british
SELECT CAST('2005-06-14 00:00:00' AS DATETIME)
There are 2 safe date formats in SQL Server:
yyyymmdd
and
yyyy-mm-ddThh:mm:ss
It is _not_ a good idea to always retrieve the datetime in a known string
format. Just retrieve the datetime as datetime, and let your application and
your user decide how to display is in a human-readable format. A properly
designed application will just use the Regional Settings from Windows to
decide how to display dates, and if you return datetime in a string format,
you just end up converting datetime values twice.
Jacco Schalkwijk
SQL Server MVP
"Andrew D. Newbould" <newsgroups@.NOzadSPANsoft.com> wrote in message
news:n0wpLfBXbrpCFwsj@.zadsoft.gotadsl.co.uk...
> In message <ep1Idw#aFHA.580@.TK2MSFTNGP15.phx.gbl>, ramjee
> <ramjee_t@.infosys.com> writes
> Not exactly. Physically in the database it is always stored the same way
> however, the collation order does determine some of the supported formats
> displaying and updating a DateTime field.
>
> The default collation order is set when the instance of MSDE is installed.
> However under MSDE 2000 / SQL Server 2000 the collation order of each
> database can be different. Thats up to you when you CREATE the DATABASE
> (ie: you determine the default collation order for each database). In
> addtion, you can specify the collation order to use on each Table and
> Field if really required. Check BOL for the CREATE DATABASE and TABLE. You
> are therefore quite capable of using the same collation order for every
> instance of MSDE you install regardless of country.
>
> Its all about handling dates in a consistent manor.
> Its generally a good idea to always update a DataTime field using the
> universal format "yyyy-mm-dd hh:nn:ss". By doing this, MSDE never gets
> confused about which part is the month and day (ie: 2005-01-05 is always
> 5th Jan whereas 05-01-2005 could be 5th Jan or 1st May). Again, this also
> solves international differences.
> Its also therefore generally a good idea to always retrieve the DateTime
> in a known format. Therefore using a command like "SELECT
> Convert(datetime, MyDateField, 102) as MyDate FROM ..." would always
> return the date in a UK format for example. That way your application does
> not get confused and the localisation to the client is left to your
> application.
>
> No Problem.
> --
> Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
> ZAD Software Systems Web : www.zadsoft.com

datepart

I am trying to use datepart to determine what row in a table a users
hiredate is closest to current system date
example
JOE was hired in Mar 01 2000
I need to get his payrate based off months experience
<12 months
<24 months
<60 months
<120 months
<200 months
Thanks
mike
something like below.
select hiredate, monthsrow from payee, payrategroup where
hiredate,
getdate(),
ltrim(datediff(month, experience_date, getdate()) / 12) + '.'
+
ltrim(datediff(month, experience_date, getdate()) % 12) as months <=
monthsrowHi
CREATE TABLE #Test
(
empl INT NOT NULL PRIMARY KEY,
hiredate DATETIME NOT NULL
)
INSERT INTO #Test VALUES (1,'20060101')
INSERT INTO #Test VALUES (2,'20060101')
INSERT INTO #Test VALUES (3,'20060409')
INSERT INTO #Test VALUES (4,'20060110')
INSERT INTO #Test VALUES (5,'20060112')
INSERT INTO #Test VALUES (6,'20060120')
INSERT INTO #Test VALUES (7,'20060108')
INSERT INTO #Test VALUES (8,'20060103')
DECLARE @.dt DATETIME
SET @.dt ='20060115' --desired date
SELECT TOP 1 WITH TIES *
FROM #Test WHERE hiredate>'20050101' AND hiredate < DATEADD(day,1,@.dt)
ORDER BY hiredate DESC
<ciojr@.yahoo.com> wrote in message
news:1144550863.151230.197030@.t31g2000cwb.googlegroups.com...
>I am trying to use datepart to determine what row in a table a users
> hiredate is closest to current system date
> example
> JOE was hired in Mar 01 2000
> I need to get his payrate based off months experience
> <12 months
> <24 months
> <60 months
> <120 months
> <200 months
> Thanks
> mike
> something like below.
> select hiredate, monthsrow from payee, payrategroup where
> hiredate,
> getdate(),
> ltrim(datediff(month, experience_date, getdate()) / 12) + '.'
> +
> ltrim(datediff(month, experience_date, getdate()) % 12) as months <=
> monthsrow
>|||Hi Mike,
Can you give the ddls and the expected output. The question seems to be
a bit confusing.|||not what I am looking for.

Dateformat

How can I determine current
SET DATEFORMAT ?DBCC USEROPTIONS
Martin C K Poon
Senior Analyst Programmer
====================================
"Alur" <Alur@.discussions.microsoft.com> bl
news:D2C70A61-AD21-4EFE-A945-5A7A72EBF6B7@.microsoft.com g...
> How can I determine current
> SET DATEFORMAT ?|||If you haven't localized SQL Server for your language, the default date
format is the American one: DD/MM/YY.
Use
SELECT GETDATE() and this will show you the default currently.
"Alur" wrote:

> How can I determine current
> SET DATEFORMAT ?|||> SELECT GETDATE() and this will show you the default currently.
No, the presentation of datetime has no correlation of how input of datetime
strings are
interpreted. See http://www.karaszi.com/SQLServer/info_datetime.asp for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Derekman" <Derekman@.discussions.microsoft.com> wrote in message
news:23BB0F57-2A93-4E20-95C8-7E5038756D51@.microsoft.com...
> If you haven't localized SQL Server for your language, the default date
> format is the American one: DD/MM/YY.
> Use
> SELECT GETDATE() and this will show you the default currently.
> "Alur" wrote:
>|||Sorry, Derekman, but this is not correct.
The American format, the default, is MDY.
Also, GETDATE() has nothing to do with DATEFORMAT.
DATEFORMAT shows you how SQL Server interprets incoming strings as dates.
For example, if you ask it to convert '3/4/06' to a datetime, will it be
April 3rd or March 4th?
SELECT CONVERT(datetime, '3/4/06')
DATEFORMAT tells SQL Server how to interpret a string that has all numbers,
which number is the month,
which is day and which is year. For the default MDY, it means the first
number is month, so '3/4/06' would be March 4th.
GETDATE returns the current date and time in a default output format, which
is based on your regional settings.
To DISPLAY a datetime in another format, you need to convert it to a string,
and specify a style. You can see the different styles available if you read
about CONVERT in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
"Derekman" <Derekman@.discussions.microsoft.com> wrote in message
news:23BB0F57-2A93-4E20-95C8-7E5038756D51@.microsoft.com...
> If you haven't localized SQL Server for your language, the default date
> format is the American one: DD/MM/YY.
> Use
> SELECT GETDATE() and this will show you the default currently.
> "Alur" wrote:
>|||DBCC USEROPTIONS
Another solution is to write some code similar to this:
SET DATEFORMAT ydm
GO
DECLARE @.datevar datetime
SET @.datevar = '01/02/03'
SELECT cast(datepart(month,@.datevar)as char(1))
+ cast(datepart(day,@.datevar)as char(1))
SELECT case cast(datepart(month,@.datevar)as char(1))
+ cast(datepart(day,@.datevar)as char(1))
when 31 then 'dym'
when 21 then 'dmy'
when 12 then 'mdy'
when 13 then 'myd'
when 32 then 'ydm'
when 23 then 'ymd'
else '?'
end
"Alur" wrote:

> How can I determine current
> SET DATEFORMAT ?|||Hi Greg
This is a really solution! I hope you don't mind that I cleaned it up
just a bit:
SET DATEFORMAT ydm; -- For testing
GO
DECLARE @.datevar datetime,
@.datecode char(2);
SET @.datevar = '01/02/03'
SELECT @.datecode = cast(datepart(month,@.datevar)as char(1))
+ cast(datepart(day,@.datevar)as char(1));
SELECT @.datecode AS datecode; -- For troubleshooting
SELECT CASE @.datecode
when '31' then 'dym'
when '21' then 'dmy'
when '12' then 'mdy'
when '13' then 'myd'
when '32' then 'ydm'
when '23' then 'ymd'
else '?'
END AS DATEFORMAT;
HTH
Kalen Delaney, SQL Server MVP
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:E90E6795-5952-4D4F-AAB5-00B764EB5130@.microsoft.com...
> DBCC USEROPTIONS
> Another solution is to write some code similar to this:
> SET DATEFORMAT ydm
> GO
> DECLARE @.datevar datetime
> SET @.datevar = '01/02/03'
> SELECT cast(datepart(month,@.datevar)as char(1))
> + cast(datepart(day,@.datevar)as char(1))
> SELECT case cast(datepart(month,@.datevar)as char(1))
> + cast(datepart(day,@.datevar)as char(1))
> when 31 then 'dym'
> when 21 then 'dmy'
> when 12 then 'mdy'
> when 13 then 'myd'
> when 32 then 'ydm'
> when 23 then 'ymd'
> else '?'
> end
> "Alur" wrote:
>|||No problem on the clean up. New script is much better.
"Kalen Delaney" wrote:

> Hi Greg
> This is a really solution! I hope you don't mind that I cleaned it up
> just a bit:
> SET DATEFORMAT ydm; -- For testing
> GO
> DECLARE @.datevar datetime,
> @.datecode char(2);
> SET @.datevar = '01/02/03'
> SELECT @.datecode = cast(datepart(month,@.datevar)as char(1))
> + cast(datepart(day,@.datevar)as char(1));
> SELECT @.datecode AS datecode; -- For troubleshooting
> SELECT CASE @.datecode
> when '31' then 'dym'
> when '21' then 'dmy'
> when '12' then 'mdy'
> when '13' then 'myd'
> when '32' then 'ydm'
> when '23' then 'ymd'
> else '?'
> END AS DATEFORMAT;
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:E90E6795-5952-4D4F-AAB5-00B764EB5130@.microsoft.com...
>
>|||Thank you very much.|||Thank you.