Is it possible with SQL date functions to have a recurring query that runs
on say the 15th of every month (that part, I know, is easily scheduled) that
will pull the following month in it's entirety? For instance, I want an
automated script to run every month on the 15th that will give me every one
that purchased a specific product on nay day of the following month. Thanks
for you help.
WillieWillie
what do you want to do with the data once you have it? (ie insert into
a talbe)
Also let me make sure i understand what you are looking for;
you want to know for a product that was order a month in the past on
the day you run this (ie jan/15/2006 would get data from Dec/15/2005).
Is that correct?|||Asuming that you want to get a report of items purchased in the PREVIOUS
month executed on the 15th, here is a suggestion:
-- BEGIN SCRIPT
set nocount on
declare @.date datetime
-- Creating a Item Purchased Table
declare @.Items table(Item varchar(50), PurchDate datetime)
-- Populating the tables
insert into @.items
values ('Item1', '12/15/2005')
insert into @.items
values ('Item2', '12/15/2005')
insert into @.items
values ('Item1', '12/18/2005')
insert into @.items
values ('Item3', '12/17/2005')
insert into @.items
values ('Item1', '12/29/2005')
insert into @.items
values ('Item4', '12/15/2005')
insert into @.items
values ('Item1', '01/14/2006')
set @.date = '01/15/2006' -- getdate() on the real script
-- Only executes on the 15th date of the month
if (select DATEPART(dd, @.date)) = 15
begin
select Item
, PurchDate
from @.Items
where PurchDate between DATEADD(day, -30, @.date) and (@.date)
end
set nocount off
except for the table and the insert statements (I just created them as an
example) you can add this script in to run every day, however it will only
execute on the 15th of the month and it will go back 30 days to get a report
of the items purchased and the correspondent dates.
Let me know if it helps.
"Willie Bodger" wrote:
> Is it possible with SQL date functions to have a recurring query that runs
> on say the 15th of every month (that part, I know, is easily scheduled) th
at
> will pull the following month in it's entirety? For instance, I want an
> automated script to run every month on the 15th that will give me every on
e
> that purchased a specific product on nay day of the following month. Thank
s
> for you help.
> Willie
>
>sql
Showing posts with label functions. Show all posts
Showing posts with label functions. Show all posts
Thursday, March 29, 2012
Monday, March 19, 2012
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 w
ends &
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?
>
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 w
ends &working days. Another alternative is to use a logic in your SELECT statement
that eliminates the w
ends ( 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="includeW
ends">Include wends in count.</param>/// <returns>Number of days.</returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static int DaysBetween(DateTime start, DateTime end, bool
includeW
ends){
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 (includeW
ends)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 functions
Hi all,
Are there a functions for last day of the month or first day of the month?
Thanks in advance,
Jen
--
Fast Track On Line -Web Design and Development
Portfolio http://www.fasttrackonline.co.uk
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004Hi, Jenny!
You wrote on Fri, 21 May 2004 12:58:00 +0100:
J> Are there a functions for last day of the month
dateadd(month,1,dateadd(day,-day(getdate())+1,getdate()))
J> or first day of the month?
dateadd(day,-day(getdate())+1,getdate())
J> Thanks in advance,
NE ZA CHTO, ZAKHODITE ESCHYO!
J> Jen
-
exexe!|||"Jenny" <jennyysplace@.eidosnet.co.uk> wrote in message news:40adef3e@.212.67.96.135...
> Hi all,
> Are there a functions for last day of the month or first day of the month?
> Thanks in advance,
> Jen
>
> --
> Fast Track On Line -Web Design and Development
> Portfolio http://www.fasttrackonline.co.uk
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004
Last day of month:
SELECT DATEADD(MONTH, 1, CURRENT_TIMESTAMP) -
DAY(DATEADD(MONTH, 1, CURRENT_TIMESTAMP))
First day of month:
SELECT CURRENT_TIMESTAMP - DAY(CURRENT_TIMESTAMP) + 1
--
JAG
Thursday, March 8, 2012
Date format reloaded
Hi all.
How can i make sql 2000 server return dd/mm/yyyy instead of mm/dd/yyyy?
I know i can make formatting functions for that, but in this case i need to
set
the sql server locale correctly.
Thanks.
Sharon.SQL Server doesn't format datetime results, the client application does. So
you need to look for a
setting in the client app you are using. Some client application respect the
Regional Settings on
the machine where you run the client app. More info at:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sharon" <nothing@.null.void> wrote in message news:eEp0uprDGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi all.
> How can i make sql 2000 server return dd/mm/yyyy instead of mm/dd/yyyy?
> I know i can make formatting functions for that, but in this case i need t
o set
> the sql server locale correctly.
> Thanks.
> --
> Sharon.
>|||Thanks Tibor.
The problem was in the data source settings.
Sharon.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23CuxEurDGHA.2300@.TK2MSFTNGP15.phx.gbl...
> SQL Server doesn't format datetime results, the client application does.
> So you need to look for a setting in the client app you are using. Some
> client application respect the Regional Settings on the machine where you
> run the client app. More info at:
> http://www.karaszi.com/SQLServer/info_datetime.asp
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sharon" <nothing@.null.void> wrote in message
> news:eEp0uprDGHA.1288@.TK2MSFTNGP09.phx.gbl...
>
How can i make sql 2000 server return dd/mm/yyyy instead of mm/dd/yyyy?
I know i can make formatting functions for that, but in this case i need to
set
the sql server locale correctly.
Thanks.
Sharon.SQL Server doesn't format datetime results, the client application does. So
you need to look for a
setting in the client app you are using. Some client application respect the
Regional Settings on
the machine where you run the client app. More info at:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sharon" <nothing@.null.void> wrote in message news:eEp0uprDGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi all.
> How can i make sql 2000 server return dd/mm/yyyy instead of mm/dd/yyyy?
> I know i can make formatting functions for that, but in this case i need t
o set
> the sql server locale correctly.
> Thanks.
> --
> Sharon.
>|||Thanks Tibor.
The problem was in the data source settings.
Sharon.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23CuxEurDGHA.2300@.TK2MSFTNGP15.phx.gbl...
> SQL Server doesn't format datetime results, the client application does.
> So you need to look for a setting in the client app you are using. Some
> client application respect the Regional Settings on the machine where you
> run the client app. More info at:
> http://www.karaszi.com/SQLServer/info_datetime.asp
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sharon" <nothing@.null.void> wrote in message
> news:eEp0uprDGHA.1288@.TK2MSFTNGP09.phx.gbl...
>
Subscribe to:
Posts (Atom)