Day of the week

Hi group ,
Please let me know which formule is usefull for calculate the day of week
based in a date.
my date is datetime.
day = convert(int,MyDate) % 7
is't working
Look up DATENAME & DATEPART functions in SQL Server Books Online.
Anith
|||SELECT DATENAME(dw, getdate())
|||But I need the number of the week. thanks you anyway.
Best regard
MArio
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:BBA8C3BE-0086-49B4-B109-843FEEBED93D@.microsoft.com...
> SELECT DATENAME(dw, getdate())
|||? number of the week, or day of the week? Your message and subject don't
agree.
In any case, as Anith suggested, look at DATENAME and DATEPART in Books
Online.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#gi0eLENEHA.2640@.TK2MSFTNGP12.phx.gbl...
> But I need the number of the week. thanks you anyway.
|||I like this formula:
(@.@.DATEFIRST + DATEPART(dw, date) ) % 7
It is always
0 on Sunday
1 on Monday
2 on Monday
up to
6 on Friday

Day of the week

Hi group ,
Please let me know which formule is usefull for calculate the day of week
based in a date.
my date is datetime.
day = convert(int,MyDate) % 7
is't workingLook up DATENAME & DATEPART functions in SQL Server Books Online.
--
Anith|||But I need the number of the week. thanks you anyway.
Best regard
MArio
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:BBA8C3BE-0086-49B4-B109-843FEEBED93D@.microsoft.com...
> SELECT DATENAME(dw, getdate())|||? number of the week, or day of the week? Your message and subject don't
agree.
In any case, as Anith suggested, look at DATENAME and DATEPART in Books
Online.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#gi0eLENEHA.2640@.TK2MSFTNGP12.phx.gbl...
> But I need the number of the week. thanks you anyway.sql

Day of the week

Hi group ,
Please let me know which formule is usefull for calculate the day of week
based in a date.
my date is datetime.
day = convert(int,MyDate) % 7
is't workingLook up DATENAME & DATEPART functions in SQL Server Books Online.
Anith|||SELECT DATENAME(dw, getdate())|||But I need the number of the week. thanks you anyway.
Best regard
MArio
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:BBA8C3BE-0086-49B4-B109-843FEEBED93D@.microsoft.com...
> SELECT DATENAME(dw, getdate())|||? number of the week, or day of the week? Your message and subject don't
agree.
In any case, as Anith suggested, look at DATENAME and DATEPART in Books
Online.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#gi0eLENEHA.2640@.TK2MSFTNGP12.phx.gbl...
> But I need the number of the week. thanks you anyway.|||I like this formula:
(@.@.DATEFIRST + DATEPART(dw, date) ) % 7
It is always
0 on Sunday
1 on Monday
2 on Monday
up to
6 on Friday

Dates in a SQL Command

hi,

i have to return all the dates in a date interval, but they are not stored in a table. what i need is to calculate all the dates between 2 given dates.

is it possible to do this in a SQL command ?

thanks,

levogiro.

levogiro:

One way to do this is to loop through a table of numbers. Here the table "small_iterator" is simply a table of integers 1-32767:

declare @.dayDiff integer
declare @.firstDate datetime set @.firstDate = '12/1/6'
declare @.secondDate datetime set @.secondDate = '1/3/7'

select @.dayDiff = datediff (day, @.firstDate, @.secondDate)
--select @.dayDiff as [@.dayDiff]

select convert (varchar (10), @.firstDate + iter - 1, 101) as rangeDate
from small_iterator (nolock)
where iter <= @.dayDiff + 1

-- Sample Output:

-- rangeDate
-- -
-- 12/01/2006
-- 12/02/2006
-- ...
-- 01/03/2007

Another solution is to use a calendar table as described in this article:

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

This article describes the use of a "Numbers" table:

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

I hope this helps.

Dave

DateDiff: Calculating working days

I am trying to use the DateDiff function to calculate the difference between two dates in working days only... Is this possible in SSRS 2005, or can anyone suggest an alternate solution?

Yes, this is possible; however, normally you must account for holidays and when all is said and done I would suggest that you give a look to this article:

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

This discusses establishing a calendar table which ought to help with computing "working days".

( Adolf: what does "NB" stand for? )

|||NB this article doesn't discuss different sets of holidays

e.g. england, wales, scotland, ireland

have different public holidays despite being in the UK

you may wish to add extra columns to cater for this|||thank you.. I've decided to pinch the calendar table off another db and will try to follow the examples.

DateDiff: Calculating working days

I am trying to use the DateDiff function to calculate the difference between two dates in working days only... Is this possible in SSRS 2005, or can anyone suggest an alternate solution?

Yes, this is possible; however, normally you must account for holidays and when all is said and done I would suggest that you give a look to this article:

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

This discusses establishing a calendar table which ought to help with computing "working days".

( Adolf: what does "NB" stand for? )

|||NB this article doesn't discuss different sets of holidays

e.g. england, wales, scotland, ireland

have different public holidays despite being in the UK

you may wish to add extra columns to cater for this|||thank you.. I've decided to pinch the calendar table off another db and will try to follow the examples.

DateDiff years as a float

Hello,
I would like to calculate the diferrence between two dates and express the
result as a float of the number of years - such as 3.75 or 5.33333. I am
trying...
CONVERT(float, DATEDIFF(d, MyStartDate, getdate() ) ) /365 as YearsOld
(I realize that dividing by 365 is inaccurate, but it is close enough for my
purposes here)
This line truncates the result to 3.0 or 5.0. What do I need to change in
the syntax?
Thanks in advanceHi Mark,
I get the corretn result when I do this
select CONVERT(float, DATEDIFF(d, convert(datetime,'1 jan 2000'),
getdate() ) ) /365 as YearsOld
but to make sure what you can do is this
select CONVERT(float, DATEDIFF(d, convert(datetime,'1 jan 2000'),
getdate() ) ) / CONVERT(float,365) as YearsOld
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Mark Hoffy" <mark@.here.com> wrote in message
news:0nuIe.290$Zo3.52@.fe03.lga...
> Hello,
> I would like to calculate the diferrence between two dates and express the
> result as a float of the number of years - such as 3.75 or 5.33333. I am
> trying...
> CONVERT(float, DATEDIFF(d, MyStartDate, getdate() ) ) /365 as YearsOld
> (I realize that dividing by 365 is inaccurate, but it is close enough for
> my
> purposes here)
> This line truncates the result to 3.0 or 5.0. What do I need to change in
> the syntax?
> Thanks in advance
>
>

DateDiff That Only Counts Working Days

Hi! I'm trying to create a query to calculate the number of days between two dates, but I only want to include working days. Is there a way to do this?Most DBAs work seven days a week, weekends and holidays included ;). The provided DateDiff works fine for us!

On a (very slightly) more serious note, what constitutes a "working day" by your definition?

-PatP|||LOL, I definitely realize what days DBAs work... :)

Working days are just the typical Monday through Friday.|||Vacations? Holidays? Snow days? Office closed due to threat of terrorist attack? Those all affect "working days". Or did you mean to ask "I'm trying to create a query to calculate the number of weekdays between two dates, excluding weekends."?|||Would you include Holidays as work days?

IF not I would suggest you have a table calendar with field workday
values 1 , 0

sum the workday field to get your answer.|||Yes, this is what I'm trying to say:

"I'm trying to create a query to calculate the number of weekdays between
two dates, excluding weekends."|||http://www.aspfaq.com/show.asp?id=2453

DateDiff Function, VERY IMPORTANT!!

I have 2 dates one a parameter and one from a field, I need to calculate the
number of days inside a reporting services expression window. The Datediff
function does not seem to work, I cannot do it on the SQL Query because one
of the dates is from a parameter (ie. @.date)You can use the datediff in your query.
SELECT DATEDIFF(day, pubdate, @.date) AS no_of_days
FROM titles where pubdate > @.date
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DragonVic" <DragonVic@.discussions.microsoft.com> wrote in message
news:AF5A3BFA-C95D-4967-B226-16CE4B4A69EB@.microsoft.com...
>I have 2 dates one a parameter and one from a field, I need to calculate
>the
> number of days inside a reporting services expression window. The Datediff
> function does not seem to work, I cannot do it on the SQL Query because
> one
> of the dates is from a parameter (ie. @.date)|||You can also do it in the expression using VB datediff ie
=datediff(DateInterval.Day,Parameters!myparm.Value,Today())
I prefer doing this stuff in sql like Mike does tho...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DragonVic" <DragonVic@.discussions.microsoft.com> wrote in message
news:AF5A3BFA-C95D-4967-B226-16CE4B4A69EB@.microsoft.com...
>I have 2 dates one a parameter and one from a field, I need to calculate
>the
> number of days inside a reporting services expression window. The Datediff
> function does not seem to work, I cannot do it on the SQL Query because
> one
> of the dates is from a parameter (ie. @.date)

datediff function

can i do a condition datediff eg
DATEDIFF(day, mydatecolumn, GETDATE()) as numberofdays
but i want to only calculate the days where mydatecolumn isn't null - is
this possible ?
Select DATEDIFF(day, mydatecolumn, GETDATE()) as numberofdays
>From SomeTable
where mydatecolumn IS NOT NULL
Or what do you want to to withthese rows not containing a date ?
Inserting 0 ?
Select ISNULL(DATEDIFF(day, mydatecolumn, GETDATE()),0) as numberofdays

>From SomeTable
where mydatecolumn IS NOT NULL
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Sorry, corrected second one.
Inserting 0 ?
Select ISNULL(DATEDIFF(day, mydatecolumn, GETDATE()),0) as numberofdays

>From SomeTable
|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1126605935.965506.214820@.g47g2000cwa.googlegr oups.com...
> Sorry, corrected second one.
> Inserting 0 ?
>
> Select ISNULL(DATEDIFF(day, mydatecolumn, GETDATE()),0) as numberofdays
>
thanks for the replies, did the job great, now how can i do this ?
(
select isnull(datediff(day,mydatecolumn1,mydatecolumn2),0 ) as numberofdays
if mydatecolumn2 is null then select
isnull(datediff(day,mydatecolumn1,GETDATE()),0) as numberofdays2 )
sorry to be a PITA
mark
|||As a batch that should be (two resultssets)
select isnull(datediff(day,mydatecolumn1,mydatecolumn2),0 ) as
numberofdays
if mydatecolumn2 is null
select isnull(datediff(day,mydatecolumn1,GETDATE()),0) as
numberofdays2
or in one statement
select isnull(datediff(day,mydatecolumn1,mydatecolumn2),0 ) as
numberofdays,
ISNULL(mydatecolumn2,isnull(datediff(day,mydatecol umn1,GETDATE()),0),0)
as numberofdays2
Did I understand your requirement right?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1126609542.543399.165310@.g44g2000cwa.googlegr oups.com...
> As a batch that should be (two resultssets)
> select isnull(datediff(day,mydatecolumn1,mydatecolumn2),0 ) as
> numberofdays
> if mydatecolumn2 is null
> select isnull(datediff(day,mydatecolumn1,GETDATE()),0) as
> numberofdays2
> or in one statement
> select isnull(datediff(day,mydatecolumn1,mydatecolumn2),0 ) as
> numberofdays,
> ISNULL(mydatecolumn2,isnull(datediff(day,mydatecol umn1,GETDATE()),0),0)
> as numberofdays2
> Did I understand your requirement right?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
something like that
my query looks like this
select column1,column2,
datediff (day,column3,column4) as newcolumn1
then i need to do this :-
if column4 is null then select datediff(day,column3,getdate())
its almost there!, im cheating by pasting into excel !
thanks
mark
|||select column1,column2,
datediff (day,column3, ISNULL(column4, GetDate())) as newcolumn1
Nik Marshall-Blank MCSD/MCDBA
"mark" <mark@.remove.com> wrote in message
news:1126612753.14638.0@.ersa.uk.clara.net...
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1126609542.543399.165310@.g44g2000cwa.googlegr oups.com...
> something like that
> my query looks like this
> select column1,column2,
> datediff (day,column3,column4) as newcolumn1
> then i need to do this :-
> if column4 is null then select datediff(day,column3,getdate())
> its almost there!, im cheating by pasting into excel !
> thanks
> mark
>
>
>

datediff function

can i do a condition datediff eg
DATEDIFF(day, mydatecolumn, GETDATE()) as numberofdays
but i want to only calculate the days where mydatecolumn isn't null - is
this possible ?Select DATEDIFF(day, mydatecolumn, GETDATE()) as numberofdays
>From SomeTable
where mydatecolumn IS NOT NULL
Or what do you want to to withthese rows not containing a date ?
Inserting 0 ?
Select ISNULL(DATEDIFF(day, mydatecolumn, GETDATE()),0) as numberofdays

>From SomeTable
where mydatecolumn IS NOT NULL
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Sorry, corrected second one.
Inserting 0 ?
Select ISNULL(DATEDIFF(day, mydatecolumn, GETDATE()),0) as numberofdays

>From SomeTable|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1126605935.965506.214820@.g47g2000cwa.googlegroups.com...
> Sorry, corrected second one.
> Inserting 0 ?
>
> Select ISNULL(DATEDIFF(day, mydatecolumn, GETDATE()),0) as numberofdays
>
>
thanks for the replies, did the job great, now how can i do this ?
(
select isnull(datediff(day,mydatecolumn1,mydate
column2),0) as numberofdays
if mydatecolumn2 is null then select
isnull(datediff(day,mydatecolumn1,GETDAT
E()),0) as numberofdays2 )
sorry to be a PITA
mark|||As a batch that should be (two resultssets)
select isnull(datediff(day,mydatecolumn1,mydate
column2),0) as
numberofdays
if mydatecolumn2 is null
select isnull(datediff(day,mydatecolumn1,GETDAT
E()),0) as
numberofdays2
or in one statement
select isnull(datediff(day,mydatecolumn1,mydate
column2),0) as
numberofdays,
ISNULL(mydatecolumn2,isnull(datediff(day
,mydatecolumn1,GETDATE()),0),0)
as numberofdays2
Did I understand your requirement right?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1126609542.543399.165310@.g44g2000cwa.googlegroups.com...
> As a batch that should be (two resultssets)
> select isnull(datediff(day,mydatecolumn1,mydate
column2),0) as
> numberofdays
> if mydatecolumn2 is null
> select isnull(datediff(day,mydatecolumn1,GETDAT
E()),0) as
> numberofdays2
> or in one statement
> select isnull(datediff(day,mydatecolumn1,mydate
column2),0) as
> numberofdays,
> ISNULL(mydatecolumn2,isnull(datediff(day
,mydatecolumn1,GETDATE()),0),0)
> as numberofdays2
> Did I understand your requirement right?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
something like that
my query looks like this
select column1,column2,
datediff (day,column3,column4) as newcolumn1
then i need to do this :-
if column4 is null then select datediff(day,column3,getdate())
its almost there!, im cheating by pasting into excel !
thanks
mark|||select column1,column2,
datediff (day,column3, ISNULL(column4, GetDate())) as newcolumn1
Nik Marshall-Blank MCSD/MCDBA
"mark" <mark@.remove.com> wrote in message
news:1126612753.14638.0@.ersa.uk.clara.net...
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1126609542.543399.165310@.g44g2000cwa.googlegroups.com...
> something like that
> my query looks like this
> select column1,column2,
> datediff (day,column3,column4) as newcolumn1
> then i need to do this :-
> if column4 is null then select datediff(day,column3,getdate())
> its almost there!, im cheating by pasting into excel !
> thanks
> mark
>
>
>

DateDiff calculating ages incorrectly.

Hi,
I am trying to use datediff to calculate a persons age, based on their date
of brith. I am using the following function:
(datediff(year,[DOB],getdate()))
The formula calculates the ages correctly for people whose birthday falls on
a day and month before today (getdate()), but for those with a birthday afte
r
today it adds an extra year on.
Anyone got any suggestions about how to correctly calculate ages using a
date of birth?Actually, DateDiff, just counts the number of <DateInterval> "boundaries"
exist between the two dates... So from 1 Jan 2004 to 31 Dec 2005 is the same
as between 31 Dec 2004 and 1 Jan 2005, There's one Year Boundary between
bothe sets of dates...
To calculate Age, use the following:
Year(@.D2) - Year(@.D1)
- Case When Month(@.D2) > Month(@.D1) Or
(Month(@.D2)= Month(@.D1) And Day(@.D2) < Day(@.D1)) Then 1
Else 0 End
You could put this in UDF...
Create FUNCTION dbo.Age (@.DOB DateTime, @.CurDT DateTime)
RETURNS TinyInt
As
Begin
Declare @.Age SmallInt
Set @.Age = Year(@.CurDT) - Year(@.DOB) -
Case When Month(@.CurDT) < Month(@.DOB) Then 1
When Month(@.CurDT) > Month(@.DOB) Then 0
When Day(@.CurDT) < Day(@.DOB) Then 1
Else 0 End
Return @.Age
End
"Enterprise Andy" wrote:

> Hi,
> I am trying to use datediff to calculate a persons age, based on their dat
e
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
on
> a day and month before today (getdate()), but for those with a birthday af
ter
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?|||http://groups.google.ca/group/micro...49c2e
c8
AMB
"Enterprise Andy" wrote:

> Hi,
> I am trying to use datediff to calculate a persons age, based on their dat
e
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
on
> a day and month before today (getdate()), but for those with a birthday af
ter
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?|||Hi Andy,
"Enterprise Andy" <EnterpriseAndy@.discussions.microsoft.com> wrote in
message news:10C0C6CD-965D-4608-A47E-E720ACE40DDE@.microsoft.com...
> Hi,
> I am trying to use datediff to calculate a persons age, based on their
> date
> of brith. I am using the following function:
> (datediff(year,[DOB],getdate()))
> The formula calculates the ages correctly for people whose birthday falls
> on
> a day and month before today (getdate()), but for those with a birthday
> after
> today it adds an extra year on.
> Anyone got any suggestions about how to correctly calculate ages using a
> date of birth?
Try this:
CREATE FUNCTION uf_YearsDifference (@.initialDate DATETIME,
@.finalDateDATETIME)
RETURNS INT
AS
BEGIN
RETURN(
SELECT CASE WHEN
DATEADD(YEAR, DATEDIFF(YEAR, @.initialDate, @.finalDate), @.initialDate) >
@.finalDate
THEN DATEDIFF(YEAR, @.initialDate, @.finalDate) - 1
ELSE DATEDIFF(YEAR, @.initialDate, @.finalDate)
END
)
END
Andrea - www.absistemi.it|||Many thanks. Saved me a lot of trouble!!!
"CBretana" wrote:
> Actually, DateDiff, just counts the number of <DateInterval> "boundaries"
> exist between the two dates... So from 1 Jan 2004 to 31 Dec 2005 is the sa
me
> as between 31 Dec 2004 and 1 Jan 2005, There's one Year Boundary between
> bothe sets of dates...
> To calculate Age, use the following:
> Year(@.D2) - Year(@.D1)
> - Case When Month(@.D2) > Month(@.D1) Or
> (Month(@.D2)= Month(@.D1) And Day(@.D2) < Day(@.D1)) Then 1
> Else 0 End
> You could put this in UDF...
> Create FUNCTION dbo.Age (@.DOB DateTime, @.CurDT DateTime)
> RETURNS TinyInt
> As
> Begin
> Declare @.Age SmallInt
> Set @.Age = Year(@.CurDT) - Year(@.DOB) -
> Case When Month(@.CurDT) < Month(@.DOB) Then 1
> When Month(@.CurDT) > Month(@.DOB) Then 0
> When Day(@.CurDT) < Day(@.DOB) Then 1
> Else 0 End
> Return @.Age
> End
>
> "Enterprise Andy" wrote:
>

datediff

Hi,
I'd like to calculate the days between 2 dates (data type:datetime).
unfortunately all I get in the preview report is #error and nothing
else. the 2 used date fields work when used seperately.
=DateDiff("day", Max(Fields!Date.Value), Min(Fields!Date.Value))
thanks!instead of using the word "day" use "d"
"florian" <florian.stammer@.gmail.com> wrote in message
news:1153840449.717417.120890@.i42g2000cwa.googlegroups.com...
> Hi,
> I'd like to calculate the days between 2 dates (data type:datetime).
> unfortunately all I get in the preview report is #error and nothing
> else. the 2 used date fields work when used seperately.
> =DateDiff("day", Max(Fields!Date.Value), Min(Fields!Date.Value))
>
> thanks!
>|||thanks it works!!!!
Ben Watts schrieb:
> instead of using the word "day" use "d"
> "florian" <florian.stammer@.gmail.com> wrote in message
> news:1153840449.717417.120890@.i42g2000cwa.googlegroups.com...
> > Hi,
> >
> > I'd like to calculate the days between 2 dates (data type:datetime).
> > unfortunately all I get in the preview report is #error and nothing
> > else. the 2 used date fields work when used seperately.
> >
> > =DateDiff("day", Max(Fields!Date.Value), Min(Fields!Date.Value))
> >
> >
> > thanks!
> >