date question

Hi,
I have a date question.
How to translate 365 days to " 1 Year 0 Month 0 Day " or 396 days to " 1
Year 1 Month 1 Day "?
Is any Date Function can do this?
Thanks!
Angi
There is nothing built in that would do this; you would have to write your
own scalar function (this sounds a lot like the common "numbers to words"
problem).
http://www.aspfaq.com/
(Reverse address to reply.)
"angi" <angi@.microsoft.public.sqlserver.olap> wrote in message
news:eLGgtgquEHA.3552@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a date question.
> How to translate 365 days to " 1 Year 0 Month 0 Day " or 396 days to " 1
> Year 1 Month 1 Day "?
> Is any Date Function can do this?
> Thanks!
> Angi
>
|||angi wrote:
> Hi,
> I have a date question.
> How to translate 365 days to " 1 Year 0 Month 0 Day " or 396 days to
> " 1 Year 1 Month 1 Day "?
> Is any Date Function can do this?
> Thanks!
> Angi
You really need to know the exact year in order to do this because you
need to account for leap-years. So you might be better off using a start
and end date, rather than a number of days. You can then run datefiff on
the dates to get the difference in years, months, and days using three
separate calls. See DATEDIFF on BOL.
David Gugick
Imceda Software
www.imceda.com
|||That's a good point David.
In which case, a calendar table might be useful.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eqPlXwquEHA.3416@.TK2MSFTNGP09.phx.gbl...
> angi wrote:
> You really need to know the exact year in order to do this because you
> need to account for leap-years. So you might be better off using a start
> and end date, rather than a number of days. You can then run datefiff on
> the dates to get the difference in years, months, and days using three
> separate calls. See DATEDIFF on BOL.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
sql

date question

Hi,
I have a date question.
How to translate 365 days to " 1 Year 0 Month 0 Day " or 396 days to " 1
Year 1 Month 1 Day "?
Is any Date Function can do this?
Thanks!
AngiThere is nothing built in that would do this; you would have to write your
own scalar function (this sounds a lot like the common "numbers to words"
problem).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"angi" <angi@.microsoft.public.sqlserver.olap> wrote in message
news:eLGgtgquEHA.3552@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a date question.
> How to translate 365 days to " 1 Year 0 Month 0 Day " or 396 days to " 1
> Year 1 Month 1 Day "?
> Is any Date Function can do this?
> Thanks!
> Angi
>|||angi wrote:
> Hi,
> I have a date question.
> How to translate 365 days to " 1 Year 0 Month 0 Day " or 396 days to
> " 1 Year 1 Month 1 Day "?
> Is any Date Function can do this?
> Thanks!
> Angi
You really need to know the exact year in order to do this because you
need to account for leap-years. So you might be better off using a start
and end date, rather than a number of days. You can then run datefiff on
the dates to get the difference in years, months, and days using three
separate calls. See DATEDIFF on BOL.
--
David Gugick
Imceda Software
www.imceda.com|||That's a good point David.
In which case, a calendar table might be useful.
http://www.aspfaq.com/2519
--
http://www.aspfaq.com/
(Reverse address to reply.)
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eqPlXwquEHA.3416@.TK2MSFTNGP09.phx.gbl...
> angi wrote:
> > Hi,
> >
> > I have a date question.
> > How to translate 365 days to " 1 Year 0 Month 0 Day " or 396 days to
> > " 1 Year 1 Month 1 Day "?
> > Is any Date Function can do this?
> >
> > Thanks!
> > Angi
> You really need to know the exact year in order to do this because you
> need to account for leap-years. So you might be better off using a start
> and end date, rather than a number of days. You can then run datefiff on
> the dates to get the difference in years, months, and days using three
> separate calls. See DATEDIFF on BOL.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

date question

Hi,
I have a date question.
How to translate 365 days to " 1 Year 0 Month 0 Day " or 396 days to " 1
Year 1 Month 1 Day "?
Is any Date Function can do this?
Thanks!
AngiThere is nothing built in that would do this; you would have to write your
own scalar function (this sounds a lot like the common "numbers to words"
problem).
http://www.aspfaq.com/
(Reverse address to reply.)
"angi" <angi@.microsoft.public.sqlserver.olap> wrote in message
news:eLGgtgquEHA.3552@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a date question.
> How to translate 365 days to " 1 Year 0 Month 0 Day " or 396 days to " 1
> Year 1 Month 1 Day "?
> Is any Date Function can do this?
> Thanks!
> Angi
>|||angi wrote:
> Hi,
> I have a date question.
> How to translate 365 days to " 1 Year 0 Month 0 Day " or 396 days to
> " 1 Year 1 Month 1 Day "?
> Is any Date Function can do this?
> Thanks!
> Angi
You really need to know the exact year in order to do this because you
need to account for leap-years. So you might be better off using a start
and end date, rather than a number of days. You can then run datefiff on
the dates to get the difference in years, months, and days using three
separate calls. See DATEDIFF on BOL.
David Gugick
Imceda Software
www.imceda.com|||That's a good point David.
In which case, a calendar table might be useful.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eqPlXwquEHA.3416@.TK2MSFTNGP09.phx.gbl...
> angi wrote:
> You really need to know the exact year in order to do this because you
> need to account for leap-years. So you might be better off using a start
> and end date, rather than a number of days. You can then run datefiff on
> the dates to get the difference in years, months, and days using three
> separate calls. See DATEDIFF on BOL.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Date query question

Is there a way to run a query or a function that will
return every Saturday of the specified year in mm/dd/yyyy
format?
TIA,
VicUse DATEPART():
-- if you are using default setting
SELECT date FROM table
WHERE DATEPART(dw, date) = 7
"Vic" wrote:
> Is there a way to run a query or a function that will
> return every Saturday of the specified year in mm/dd/yyyy
> format?
> TIA,
> Vic
>|||http://www.aspfaq.com/2519
(Locale-specific format is the job of the presentation layer, btw, and
mm/dd/yyyy is not the best choice.)
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vic" <vduran@.specpro-inc.com> wrote in message
news:02de01c5361c$27813310$a501280a@.phx.gbl...
> Is there a way to run a query or a function that will
> return every Saturday of the specified year in mm/dd/yyyy
> format?
> TIA,
> Vic

Date query question

Is there a way to run a query or a function that will
return every Saturday of the specified year in mm/dd/yyyy
format?
TIA,
Vic
Use DATEPART():
-- if you are using default setting
SELECT date FROM table
WHERE DATEPART(dw, date) = 7
"Vic" wrote:

> Is there a way to run a query or a function that will
> return every Saturday of the specified year in mm/dd/yyyy
> format?
> TIA,
> Vic
>
|||http://www.aspfaq.com/2519
(Locale-specific format is the job of the presentation layer, btw, and
mm/dd/yyyy is not the best choice.)
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vic" <vduran@.specpro-inc.com> wrote in message
news:02de01c5361c$27813310$a501280a@.phx.gbl...
> Is there a way to run a query or a function that will
> return every Saturday of the specified year in mm/dd/yyyy
> format?
> TIA,
> Vic

Date query question

Is there a way to run a query or a function that will
return every Saturday of the specified year in mm/dd/yyyy
format?
TIA,
VicUse DATEPART():
-- if you are using default setting
SELECT date FROM table
WHERE DATEPART(dw, date) = 7
"Vic" wrote:

> Is there a way to run a query or a function that will
> return every Saturday of the specified year in mm/dd/yyyy
> format?
> TIA,
> Vic
>|||http://www.aspfaq.com/2519
(Locale-specific format is the job of the presentation layer, btw, and
mm/dd/yyyy is not the best choice.)
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vic" <vduran@.specpro-inc.com> wrote in message
news:02de01c5361c$27813310$a501280a@.phx.gbl...
> Is there a way to run a query or a function that will
> return every Saturday of the specified year in mm/dd/yyyy
> format?
> TIA,
> Vic

date part of getdate

i want to use the date part of the function getdate in my sql query.
the getdate function return the current date and time. i want only the date part.
how can i do it.You can use the CONVERT functions and specify a date format. However, it's a lot harder if you want to return the result as an ISO XML format...but if you're not worried about that then convert will work fine|||CONVERT(VARCHAR(10),GETDATE(),101)

Date part of a dateTime value

Hi,
This is no function in SQL Server to get only the date part of a datetime
variable, sometime, and usually most of the time, I would like to have a
query to get the result of only some specific day's, and I am not sure what
could be the best way to do this query.
For example, if I want to get all the records of my table for only todays.
Thanks in advance for your advice on this.
FrankYou can get the Date part of the date time using the datepart function.
select Convert(nvarchar(25),getdate(),100 )
You can use the datediff function for retrieving the current day's
record
Select * from table where datediff(d,getdate(),datecolumn) = 0|||Hi,
SELECT * from YOurTable
Where Datefield >= convert(VARCHAR(8),getdate(),112)
Uses an ISO Date format, you could also Convert the Datefield Column, but
this wont need to be neccessary because of causing performance issues.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is no function in SQL Server to get only the date part of a datetime
> variable, sometime, and usually most of the time, I would like to have a
> query to get the result of only some specific day's, and I am not sure
> what
> could be the best way to do this query.
> For example, if I want to get all the records of my table for only todays.
> Thanks in advance for your advice on this.
> Frank
>|||Continuation to the above message from me ...
Sorry .. use this
select Convert(nvarchar(25),getdate(),101 )
you can also check books online for the different values instead of 101
for getting the required output|||Thanks, balacr,
The second datediff(d, getdate(), datecolumn) = 0 is exactly what I want.
B/R
Frank
<balacr@.gmail.com> wrote in message
news:1113806479.336151.291970@.l41g2000cwc.googlegroups.com...
> You can get the Date part of the date time using the datepart function.
> select Convert(nvarchar(25),getdate(),100 )
> You can use the datediff function for retrieving the current day's
> record
> Select * from table where datediff(d,getdate(),datecolumn) = 0
>|||Hi Frank,
"Frank" <wangping@.lucent.com> wrote in message
news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is no function in SQL Server to get only the date part of a datetime
> variable, sometime, and usually most of the time, I would like to have a
> query to get the result of only some specific day's, and I am not sure
> what
> could be the best way to do this query.
> For example, if I want to get all the records of my table for only todays.
> Thanks in advance for your advice on this.
Use Northwind
/* (1) */
select * from orders
where
orderdate >= CAST(CONVERT(char(8), getdate(), 112) AS DATETIME) and
orderdate < DATEADD(day, 1, CAST(CONVERT(char(8), getdate(), 112) AS
DATETIME))
/* (2) */
select * from orders
where
day(orderdate) = day(getdate()) and
month(orderdate) = month(getdate()) and
year(orderdate) = year(getdate())
/* (3) */
Select * from orders
where
datediff(day ,getdate() , orderdate) = 0

> Frank
HTH,
Andrea|||Thanks, Jens,
I also noticed that the "where datediff()" method could causing performance
issues, since I was told that if in the "where clause", there is some
functions on the column, then even there is an index on that column, the
database query engine will not use it.
That is something like this,
select * from myTable where dateDiff(day, myDateColumn, getdate()) =0,
Then even on "myDateColumn" there is an index, the SQL Server will not use
that index.
Am I right?
B/R
Frank
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eNqfyI%23QFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Hi,
> SELECT * from YOurTable
> Where Datefield >= convert(VARCHAR(8),getdate(),112)
> Uses an ISO Date format, you could also Convert the Datefield Column, but
> this wont need to be neccessary because of causing performance issues.
>
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
> "Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
> news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
datetime
todays.
>|||Yeah you are, thats why i pointed out not to do something with the column
rather than doing this "static" Conversion (because it will be only
evaluated once.)
HTH, Jens Smeyer.
http:/www.sqlserver2005.de
--
"Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
news:uKcZMQ%23QFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Thanks, Jens,
> I also noticed that the "where datediff()" method could causing
> performance
> issues, since I was told that if in the "where clause", there is some
> functions on the column, then even there is an index on that column, the
> database query engine will not use it.
> That is something like this,
> select * from myTable where dateDiff(day, myDateColumn, getdate()) =0,
> Then even on "myDateColumn" there is an index, the SQL Server will not use
> that index.
> Am I right?
> B/R
> Frank
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:eNqfyI%23QFHA.3704@.TK2MSFTNGP12.phx.gbl...
> datetime
> todays.
>|||Thanks, Andrea,
I would like to choose the first of your ideas, I think there maybe some
performance issues for the second and third ones.
B/R
Frank
"Andrea Benedetti" <abenedetti@.absistemi.it> wrote in message
news:%232XsyM%23QFHA.3076@.tk2msftngp13.phx.gbl...
> Hi Frank,
> "Frank" <wangping@.lucent.com> wrote in message
> news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
datetime
todays.
> Use Northwind
> /* (1) */
> select * from orders
> where
> orderdate >= CAST(CONVERT(char(8), getdate(), 112) AS DATETIME) and
> orderdate < DATEADD(day, 1, CAST(CONVERT(char(8), getdate(), 112) AS
> DATETIME))
> /* (2) */
> select * from orders
> where
> day(orderdate) = day(getdate()) and
> month(orderdate) = month(getdate()) and
> year(orderdate) = year(getdate())
> /* (3) */
> Select * from orders
> where
> datediff(day ,getdate() , orderdate) = 0
>
> HTH,
> Andrea
>sql

Date Parsing with DateValue Equivalent

Thanks in Advance,
MS Access has a flexible function called DateValue that will convert a
valid string into a date. Does anyone know a method of parsing ntext
values to detect and convert this strings into dates? The ntext values
might look something like this:
Established in 1974.
Created in Aug 1986 and dedicated on September 29th, 1986.
Abolished January 1, 1957. Reenacted on May 15, 1978, and transferred
functions on March 11, 1981 by executive order.
I suspect a combination of patindex search and other functions will do
the trick.
Mark
Napa, CAIf you are using SQL Server 2005, you could write a User Defined Function in
a .NET language that could do this type of parsing for you.
Hope this helps!
Chuck Heinzelman
MCSD, MCDBA
I support the Professional Association for SQL Server (www.sqlpass.org)
This posting is not an endoresment of any product.
Information is provided as-is, and carries no warranties - either express or
implied.
Please respond in newsgroups only.
<xxxdbaxxx@.gmail.com> wrote in message
news:1145893343.427405.182460@.v46g2000cwv.googlegroups.com...
> Thanks in Advance,
> MS Access has a flexible function called DateValue that will convert a
> valid string into a date. Does anyone know a method of parsing ntext
> values to detect and convert this strings into dates? The ntext values
> might look something like this:
> Established in 1974.
> Created in Aug 1986 and dedicated on September 29th, 1986.
> Abolished January 1, 1957. Reenacted on May 15, 1978, and transferred
> functions on March 11, 1981 by executive order.
> I suspect a combination of patindex search and other functions will do
> the trick.
> Mark
> Napa, CA
>|||Well, we are still in SQL 2000 Chuck. Any other thoughts?

Date manipulation

I need to to get the result of the function GETDATE and converted to a simpler "mm/dd/yyyy" format in order to compare the results to another date in a table. In ACCESS the function DATE returns the format of 'mm/dd/yyyy' since I need to work with date ranges without a need for this application 'HH:MM:SS'

I have try 'TRANSFORM(GETDATE,'mm/dd/yyyy') but I keep getting errors.

I am not sure what I am doing wrong? Any help is appreciated since I need to work in SQL Server 2000.

Gratefull

Neil

If you want to store the "tranformed date" in a datetime column, the hh,mm,ss will still be added to the column, although they might be NULL, depending on your operation you use for the transform, but if you want to simply minsert the transformed/converted date into a character column you can do the following:

SELECT CONVERT(VARCHAR(10),GETDATE(),1)

See the CONVERT function in the BOL for more information.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Thanks for your help Jens, I am still making the transition from ACCESS Functions to SQL Server and how to get similar results

Date less then 13 months

How can I use datediff function so that it returns me date with less
then or equal to 13 months from today?
thanksInstead of DATEDIFF, use DATEADD function like:
DATEADD( m, -13, CURRENT_TIMESTAMP )
Anith

date Intervals

Is that like some interval function for dates, like i want to group my data in intervals of 15 minutes and 30 minutes. Is there such a function in T-SQLNot really. But you can use divide to achive this. See below for illustration
declare
@.datetable table
(
date_col datetime
)
insert into @.datetable(date_col)
select dateadd(minute, n1 + n2, '2006-01-01 01:00')
from
(
select 0 as n1 union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9
) as num1
cross join
(
select 0 as n2 union all
select 10 union all select 20 union all select 30 union all
select 40 union all select 50 union all select 60
) as num2

select date_col, datediff(minute, 0, date_col) / 15 as timeslot -- 15 mins interval
from @.datetable
order by timeslot, date_col|||Slight variation: Use MOD operatore instead of divisor, and convert back to datetime value -
select date_col,
dateadd(minute, datediff(minute, 0, date_col) % 15, 0)
from @.datetable
order by timeslot, date_col|||You can also experiment with datepart() to get parts of the date.|||This will group the time
01:00, 01:15, 01:30, 01:45 as one group,
01:01, 01:16, 01:31, 01:46 as another group.

Well, It really depends on what jcwc888 means by "intervals of 15 minutes" :)

Date functions

Hello,
I need help with my function. I need a date difference (actual datetime and
another datetime in db) in hours It worked perfectly with DATEDIFF...But
now I found out that I should count just the "working "days (Mo-Fri). is
there any function or mechanism how to do the same but just with the
MONDAY-FRIDAY?
Thanks so much in advance
ZThe best approach is to use a calendar table that list the wends &
working days. Another alternative is to use a logic in your SELECT statement
that eliminates the wends ( CASE expressions, eliminate in the WHERE/
HAVING clause etc. ). If you post your table structures, sample data &
expected data ( www.aspfaq.com/5006 ), someone here might show you how you
can do this.
Anith|||Maybe something like:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
/// <summary>
/// Returns the number of days between two dates.
/// </summary>
/// <param name="start">Start date.</param>
/// <param name="end">End date.</param>
/// <param name="includeWends">Include wends in count.</param>
/// <returns>Number of days.</returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static int DaysBetween(DateTime start, DateTime end, bool
includeWends)
{
start = start.Date;
end = end.Date;
if (start > end)
throw new ArgumentException("end date must be > start date.");
if (start == end)
return 0;
int days = ((TimeSpan)(end - start)).Days;
if (includeWends)
return days;
int bizDays = 0;
for (int i = 0; i < days; i++)
{
start = start.AddDays(1);
if (start.DayOfW == DayOfW.Saturday || start.DayOfW ==
DayOfW.Sunday)
continue;
bizDays++;
}
return bizDays;
}
};
Test
----
select dbo.DaysBetween('1/1/2005','1/3/2005', 0)
--
William Stacey [MVP]
"Zuska" <Zuska@.discussions.microsoft.com> wrote in message
news:16DBB07D-9498-4D2F-8899-6A113793BFB3@.microsoft.com...
| Hello,
| I need help with my function. I need a date difference (actual datetime
and
| another datetime in db) in hours It worked perfectly with DATEDIFF...But
| now I found out that I should count just the "working "days (Mo-Fri). is
| there any function or mechanism how to do the same but just with the
| MONDAY-FRIDAY?
|
| Thanks so much in advance
| Z
|
||||Zuska,
are you saying there are no holidays at all and every Monday is a
working day?|||"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1138652396.929823.234190@.g43g2000cwa.googlegroups.com...
> Zuska,
> are you saying there are no holidays at all and every Monday is a
> working day?
Every Monday should be a holiday. :-)|||HI,
youre right, there are more holidays then just sat or sun...it meansI should
make kind of calendar table to set all the holidays in it?
my table is easy
CREATE TABLE [dbo].[test] (
[ID] [int] NOT NULL ,
[YEAR] [int] NOT NULL ,
[TIME] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
There is a time of receiving sample in the column TIME and there is a
72hours guarantee time between time of receiving and actual time. I did it
using DATEDIFF...but it supposed to be 72 hours just in workingdays. So, if
the time in the table test on thursday 8:00AM the guarantee time ends on
tuesday 8:00AM.
thanks
Alexander Kuznetsov p_?e:

> Zuska,
> are you saying there are no holidays at all and every Monday is a
> working day?
>|||Zuska,
Once I was speeding up a system in which expressions like "2 business
days later" needed to be calculated real quick. My approach was quite
simple:
CREATE TABLE [dbo].[test] (
[ID] [int] NOT NULL ,
[WorkDayNum] int,
[date] [datetime] NOT NULL
) ON [PRIMARY]
for instance, days around Martin Luther King day would be represented
like this:
insert into test values(111, 86, '20060113')
-- Saturday
insert into test values(112, NULL, '20060114')
-- Sunday
insert into test values(113, NULL, '20060115')
-- Martin Luther King day
insert into test values(114, NULL, '20060116')
-- normal work day
insert into test values(114, NULL, '20060117')
So, calculating business days between 2 days is easy
select t2.workdaynum - t1.workdaynum
from test t1, test t1
where t1.[date] = '20051227'
and t2.[date]='20060114'
Makes sense?|||Alex, thanks for your help, youre the best:))its really nice and easy:))
Alexander Kuznetsov p_?e:

> Zuska,
> Once I was speeding up a system in which expressions like "2 business
> days later" needed to be calculated real quick. My approach was quite
> simple:
> CREATE TABLE [dbo].[test] (
> [ID] [int] NOT NULL ,
> [WorkDayNum] int,
> [date] [datetime] NOT NULL
> ) ON [PRIMARY]
> for instance, days around Martin Luther King day would be represented
> like this:
> insert into test values(111, 86, '20060113')
> -- Saturday
> insert into test values(112, NULL, '20060114')
> -- Sunday
> insert into test values(113, NULL, '20060115')
> -- Martin Luther King day
> insert into test values(114, NULL, '20060116')
> -- normal work day
> insert into test values(114, NULL, '20060117')
> So, calculating business days between 2 days is easy
> select t2.workdaynum - t1.workdaynum
> from test t1, test t1
> where t1.[date] = '20051227'
> and t2.[date]='20060114'
> Makes sense?
>

date function/tool to determine future business days/ holidays

Hi,

Does SQL server 2005 provide a function to determine future BUSINESS day ? What about holidays ? I sthere a way to feed a holiday file into a table and have SQL Server determin if the future date falls on a business day ?

As far as you do not mean the fixed calculated public holidays (like Easter, which can be calculated with a Gauss formula) you will have to implement that on your own having a custom table with the special dates.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks but I am still unclear. If I'd like to know if today's date + 100 days from now would fall on a business day . Is there a ready-to-use MS SQL function that I can use or should I write it on my own ?

|||

Well, define business day. There is not a common understanding of business day (even in one countrym like here in Germany :-) )

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hello. I think the following library of functions can help with the issue of finding Business Days:

http://www.SQLsharp.com/

I just added some date functions and one of them is finding the number of business days within a given date range. And because not everyone can agree on what is a business day, the function allows the user to select which days are to be filtered out as non-business days. I have not done a function to find if a particular day in the future is a business day, but that can be added quite easily. This function can determine holidays on any given year so you don't need the typical holiday table that most people suggest using.

date function/tool to determine future business days/ holidays

Hi,

Does SQL server 2005 provide a function to determine future BUSINESS day ? What about holidays ? I sthere a way to feed a holiday file into a table and have SQL Server determin if the future date falls on a business day ?

As far as you do not mean the fixed calculated public holidays (like Easter, which can be calculated with a Gauss formula) you will have to implement that on your own having a custom table with the special dates.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks but I am still unclear. If I'd like to know if today's date + 100 days from now would fall on a business day . Is there a ready-to-use MS SQL function that I can use or should I write it on my own ?

|||

Well, define business day. There is not a common understanding of business day (even in one countrym like here in Germany :-) )

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hello. I think the following library of functions can help with the issue of finding Business Days:

http://www.SQLsharp.com/

I just added some date functions and one of them is finding the number of business days within a given date range. And because not everyone can agree on what is a business day, the function allows the user to select which days are to be filtered out as non-business days. I have not done a function to find if a particular day in the future is a business day, but that can be added quite easily. This function can determine holidays on any given year so you don't need the typical holiday table that most people suggest using.

Date Function, Reporting, Visual Studio 2003

I would like to use a function to truncate the time of day from the date, so
that sorting will be only by date and not date/time of day.
How is this done?I ran into same issue. I have a workaround, but I really am interested to
know the solution. My workaround was to use convert(varchar,datetimefield,
101) - this removes time portion from the field, but problem with this is it
converts into varchar, so the sorting doesn't work as you would need.
Vipul
"Jim" wrote:
> I would like to use a function to truncate the time of day from the date, so
> that sorting will be only by date and not date/time of day.
> How is this done?
>

Date function to extract start date

Hello,
I was wondering if there is a date function to determine a starting
date based on a @.EndDate
parameter. In other word, if my user chose an end date of 3/31/2006, I
want to get a start date of 4/1/2005.
I appreciate any suggestion.
Edgar J.You can use another query to provide a default for the second parameter
using the 1st as input.
If its sqlserver you can use (I may have syntax slightly wrong as Im
not looking this up now).
SELECT [StartDate] = dateadd(day, dateadd(year, @.EndDate, -1), 1)
In other words return just 1 row , 1 column with a date.
Use sqlserver dateadd function to subtract 1 year and again to add 1
day.
Edgar wrote:
> Hello,
> I was wondering if there is a date function to determine a starting
> date based on a @.EndDate
> parameter. In other word, if my user chose an end date of 3/31/2006, I
> want to get a start date of 4/1/2005.
> I appreciate any suggestion.
> Edgar J.

Date function Question

Hi everone,
I want to add a criteria to filter out the Sales Order date.
For example, today is 10 Oct 2005, the default range that I want for the
user like this:
Start Order Date: 10 Sep 2005
End Order Date: 10 Oct 2005
I don't know how to set in parameter for the user! Can I use the following
function (like VB)?
=dateadd('m', -1, Globals!ExecutionTime) ==> for Start Order Date
Please help!
TonyDear all,
I can fix the problem, the correct one should be:
=dateadd("m", -1, Globals!ExecutionTime)
For your reference!!!!!
Tony
"Tony" wrote:
> Hi everone,
> I want to add a criteria to filter out the Sales Order date.
> For example, today is 10 Oct 2005, the default range that I want for the
> user like this:
> Start Order Date: 10 Sep 2005
> End Order Date: 10 Oct 2005
> I don't know how to set in parameter for the user! Can I use the following
> function (like VB)?
> =dateadd('m', -1, Globals!ExecutionTime) ==> for Start Order Date
> Please help!
> Tony

Date Function on Redbrick or Oracle

Is the function for getting system date for BRIO operating within
Redbrick DB is:

getDate();

and for Oracle is:

SysDate();

I am trying to run a report (operating in Redbrick DB) which
captures PAST THREE MONTHS. This report has to run EVERY WEEK.

So my logic is to find out TODAY's DATE and MINUS 3 months.

Also, what is the syntax or formula to MINUS 3 months?

sysDate() - 90 ?

please assist.

Thanks.

AntonOracle,

select SYSDATE - Interval '3' Month from dual;

OR

select add_months(sysdate, -3) from dual;|||thanks.

but can I get the current date without using the dual table, if it's on oracle?

how about redbrick?

the db i'm querying on is on redbrick.|||U Can get today date in rebrick using
current_date.

Thanks,
Praveen.|||yep. thanks!

Date function in DTS package

How can I make this work against an Access table through an ODBC dsn in my DTS package? I need to subtract 1 day from the current date.

Tried this, which is incomplete of course, (need to subtract the 1 day):

WHERE (TTDateTimeIn >= DATEDIFF(dd, 1, { fn CURDATE() }))

Got this error:
[Microsoft][ODBC Microsoft Access Driver] Too few Parameters. Expected 1.I believe the function you should be using is DATEADD, not DATEDIFF. And you will need to enclose that dd in single quotes.

Terri|||That's correct, I finally figured it out. What added to my troubles was needing to pull yesterdays data after midnight, but I got it.

(TTDateTimeIn >= DATEADD('y', - 1, { fn CURDATE() }))

It was painful building this DTS package using a DSN connection to get to locked MS Access tables.

Thanks.