Get the system date format?

Hi!
I'm trying to get hold of the systems date format in my code so that I can change a date I've got. Dates are stored with default format in the database. I'm presenting a date in a messagebox (where it becomes text) and I want to change the format to the system format before I put it in the text. The date must be right with different kind of system settings, swedish, uk...

Can I do that?

I'm using:
to_char(date, 'DD/MM/YYYY')
This works, but of course the format will be uk all the time.

I dont want to write the format, because then i does not mater what my system settings are.

Hope i made my self understandable..Maybee i should have written "get the date format from regional settings".

Get Period of Dates

Hi,
Just wonder if i can get a period of dates to be inserted into a temp table (with a single field [Sales_Date]) base on a Start and End Date using a select query?
For Eg,
Start = '8/1/2005', End = '8/5/2005'
In the temp table,
8/1/2005
8/2/2005
8/3/2005
8/4/2005
8/5/2005
Your help is appreciated. Thks.
Rgds
Ryanyou could do something like :

declare @.sdate datetime, @.edate datetime
select
@.sdate = '08/11/2005'
,@.edate = '08/15/2005'
create table #t (datecol datetime)
insert into #t values (@.sdate)
while datediff(d,@.sdate, @.edate) > 0
begin
insert into #t values (dateadd(d, 1,@.sdate ))
set @.sdate = dateadd(d,1, @.sdate)
end
select * from #t
drop table #t|||

Hi,

Your method works. Anyway, aApart from using a temp table, any other more efficient way to get the same outcome? Let me know. Thks.

Ryan

|||The only more efficient way I can think of is to physcially create atable that contains all of the dates and leave that sitting on disk.

Get number of business days between dates

Hi. Is there a way to get the number of business days or hours between two dates? Also, is there a way to have it ignore weekends and U.S. holidays? Thank you.

dev:

The short answer to your question is yes, there is a compute the difference of two dates in business days. Give this article a look and consider using a calendar table.

Dave

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

|||There is nothing built in which does that. Besides everyone has different holidays federal, state, local, company?

The best way I have come up with is to create a table of non-business days, with a date field as the primary key, so you don't get holidays on Sat/Sun. And populate it for X years. If you are looking at over 10 years, this gets difficult, but not imposible.

Then you simply count the records between startdate and enddate and just subtract the count from the days.

|||The method described by Tom is the method that I have implemented most frequently; it is a good way to go.|||Thank you, Tom and Mugambo. Your suggestions worked.|||

i am using this script to get amount of business days

Code Snippet

set datefirst 1
declare @.sdate datetime
declare @.edate datetime

select @.sdate = '20070516' --for example, start date May, 16th
select @.edate='20070531' --end date May, 31st

select datediff(day, @.sdate, @.edate)+1-(
select (case datepart(dw, @.sdate)
when 7 then (datepart(ww, @.edate)-datepart(ww, @.sdate))*2-1
else (datepart(ww, @.edate)-datepart(ww, @.sdate))*2
end)+
(case datepart(dw, @.edate)
when 6 then 1
when 7 then 2
else 0
end)
)

Get number of business days between dates

Hi. Is there a way to get the number of business days or hours between two dates? Also, is there a way to have it ignore weekends and U.S. holidays? Thank you.

dev:

The short answer to your question is yes, there is a compute the difference of two dates in business days. Give this article a look and consider using a calendar table.

Dave

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

|||There is nothing built in which does that. Besides everyone has different holidays federal, state, local, company?

The best way I have come up with is to create a table of non-business days, with a date field as the primary key, so you don't get holidays on Sat/Sun. And populate it for X years. If you are looking at over 10 years, this gets difficult, but not imposible.

Then you simply count the records between startdate and enddate and just subtract the count from the days.|||The method described by Tom is the method that I have implemented most frequently; it is a good way to go.|||Thank you, Tom and Mugambo. Your suggestions worked.|||

i am using this script to get amount of business days

Code Snippet

set datefirst 1
declare @.sdate datetime
declare @.edate datetime

select @.sdate = '20070516' --for example, start date May, 16th
select @.edate='20070531' --end date May, 31st

select datediff(day, @.sdate, @.edate)+1-(
select (case datepart(dw, @.sdate)
when 7 then (datepart(ww, @.edate)-datepart(ww, @.sdate))*2-1
else (datepart(ww, @.edate)-datepart(ww, @.sdate))*2
end)+
(case datepart(dw, @.edate)
when 6 then 1
when 7 then 2
else 0
end)
)

Get Missing Dates

Hi,
I have 2 tables, one a list of people, and the other a table of daily
diary entries for those people.
Now i need to get a list of all people and dates that don't have an entry.
I have created a calendar table in order to assist but i can't figure
out how to pull back the results as i need.
What i need is something along the lines of
PersonId | Date
--
1 | '2006-01-01'
1 | '2006-01-02'
1 | '2006-01-12'
2 | '2006-01-01'
2 | '2006-01-21'
etc... | etc...
Any help would be greatly appreciated.
Many thanks
MattSo we have some idea...
http://www.aspfaq.com/5006
"Matt Brailsford" <matt@.gradiation.co.uk> wrote in message
news:OkGE6nvQGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have 2 tables, one a list of people, and the other a table of daily
> diary entries for those people.
> Now i need to get a list of all people and dates that don't have an entry.
> I have created a calendar table in order to assist but i can't figure out
> how to pull back the results as i need.
> What i need is something along the lines of
> PersonId | Date
> --
> 1 | '2006-01-01'
> 1 | '2006-01-02'
> 1 | '2006-01-12'
> 2 | '2006-01-01'
> 2 | '2006-01-21'
> etc... | etc...
> Any help would be greatly appreciated.
> Many thanks
> Matt|||Try this:
declare @.Person table (personid int)
insert @.person values (1)
insert @.person values (2)
declare @.Date table (dt datetime)
insert @.Date values ('01 Jan 2006')
insert @.Date values ('02 Jan 2006')
insert @.Date values ('03 Jan 2006')
insert @.Date values ('04 Jan 2006')
insert @.Date values ('05 Jan 2006')
insert @.Date values ('06 Jan 2006')
insert @.Date values ('07 Jan 2006')
declare @.Diary table (personid int, dt datetime)
insert @.Diary values(1, '01 Jan 2006')
insert @.Diary values(1, '03 Jan 2006')
insert @.Diary values(1, '04 Jan 2006')
insert @.Diary values(2, '05 Jan 2006')
insert @.Diary values(2, '06 Jan 2006')
select p.personid, d.dt
from @.Person p
cross join @.Date d
left outer join @.Diary e
on p.personid = e.personid and d.dt = e.dt
where e.personid is null|||Excellent,
That looks exactly what i need.
I'll give it a try.
jeff.bolton@.citigatehudson.com wrote:
> Try this:
> declare @.Person table (personid int)
> insert @.person values (1)
> insert @.person values (2)
> declare @.Date table (dt datetime)
> insert @.Date values ('01 Jan 2006')
> insert @.Date values ('02 Jan 2006')
> insert @.Date values ('03 Jan 2006')
> insert @.Date values ('04 Jan 2006')
> insert @.Date values ('05 Jan 2006')
> insert @.Date values ('06 Jan 2006')
> insert @.Date values ('07 Jan 2006')
> declare @.Diary table (personid int, dt datetime)
> insert @.Diary values(1, '01 Jan 2006')
> insert @.Diary values(1, '03 Jan 2006')
> insert @.Diary values(1, '04 Jan 2006')
> insert @.Diary values(2, '05 Jan 2006')
> insert @.Diary values(2, '06 Jan 2006')
> select p.personid, d.dt
> from @.Person p
> cross join @.Date d
> left outer join @.Diary e
> on p.personid = e.personid and d.dt = e.dt
> where e.personid is null
>