getdate

Hi
I have to create a series of reports that look back over certain days ie.
day before
current w
current month, quarter, year
The reports will be scheduled to be run between the hours of 10pm to 6am
when the business is shut.
I am using getdate - days to get my time span an example is
WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime,
CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
CONVERT(varchar(11), GETDATE() + 0, 102), 102))
this would get me my range for yesterdays results
However having run a query with the above where clause it pulled some out
for today, which make smy results wrong because it has used some of today, i
am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hours
giving me 2006-01-04 16:30:32 , this may not be a problem if the reports are
scheduled to run out of hours but is there a way of using the date and
adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
sure my reports were accurate, plus it would be nice to know
hope i made sense
regardsHave a look at the DATEDIFF function is SQL Books
HTH. Ryan
"Steven Scaife" <sp@.nospam.com> wrote in message
news:ey0ywdhEGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN
> CONVERT(datetime, CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND
> CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
> i am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24
> hours giving me 2006-01-04 16:30:32 , this may not be a problem if the
> reports are scheduled to run out of hours but is there a way of using the
> date and adding my own time constraint in eg 00:00:01 and 23:59:59 this
> would make sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>|||Hope that following syntax can help you. This will give yout todays date at
10:00 PM.
select convert(datetime,convert(char(8),getdate
(),112) + ' 22:00:00',120)
"Steven Scaife" wrote:

> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime
,
> CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
i
> am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hou
rs
> giving me 2006-01-04 16:30:32 , this may not be a problem if the reports a
re
> scheduled to run out of hours but is there a way of using the date and
> adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
> sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>
>|||For yesterday's results, try
WHERE DATEDIFF(dd, dbo.vw_MIS_AppWritten.Date_App_Written, GETDATE()) = 1
For last w's results
WHERE DATEDIFF(wk, dbo.vw_MIS_AppWritten.Date_App_Written, GETDATE()) = 1
For last month's results
WHERE DATEDIFF(mm, dbo.vw_MIS_AppWritten.Date_App_Written, GETDATE()) = 1
"Steven Scaife" wrote:

> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime
,
> CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
i
> am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hou
rs
> giving me 2006-01-04 16:30:32 , this may not be a problem if the reports a
re
> scheduled to run out of hours but is there a way of using the date and
> adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
> sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>
>|||Also you can use a calendar table|||There are several ways to remove the timestamp from getdate() and retaining
it as a datetime that I know of.
Here is what I typically do
1) SELECT CAST(CONVERT(VARCHAR(10), getdate(), 102) as DATETIME)
I would avoid the "adding your own time" and just use comparisons against
dates without times (ie. Midnight of that day).
For Instance, if i wanted all rows where dateFromTable is Today the WHERE
clause would be:
WHERE dateFromTable BETWEEN CAST(CONVERT(VARCHAR(10), getdate(), 102) as
DATETIME)
AND CAST(CONVERT(VARCHAR(10), dateadd(dd, 1, getdate()), 102) as DATETIME)
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Steven Scaife" wrote:

> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime
,
> CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
i
> am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hou
rs
> giving me 2006-01-04 16:30:32 , this may not be a problem if the reports a
re
> scheduled to run out of hours but is there a way of using the date and
> adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
> sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>
>|||This works well too
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
Returns today's date at 00:00:00.000
"Ryan Powers" wrote:
> There are several ways to remove the timestamp from getdate() and retainin
g
> it as a datetime that I know of.
> Here is what I typically do
> 1) SELECT CAST(CONVERT(VARCHAR(10), getdate(), 102) as DATETIME)
> I would avoid the "adding your own time" and just use comparisons against
> dates without times (ie. Midnight of that day).
> For Instance, if i wanted all rows where dateFromTable is Today the WHERE
> clause would be:
> WHERE dateFromTable BETWEEN CAST(CONVERT(VARCHAR(10), getdate(), 102) as
> DATETIME)
> AND CAST(CONVERT(VARCHAR(10), dateadd(dd, 1, getdate()), 102) as DATETIME)
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Steven Scaife" wrote:
>|||Nice. Thanks Mark.
That is a little cleaner that what I proposed. Good to know.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark Williams" wrote:
> This works well too
> SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
> Returns today's date at 00:00:00.000
> --
> "Ryan Powers" wrote:
>|||thank you it is much appreciated
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:0DBCD236-D731-4201-B577-558AA3708FE3@.microsoft.com...
> This works well too
> SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
> Returns today's date at 00:00:00.000
> --
> "Ryan Powers" wrote:
>

Get recordset result in variables

Hello folks.
I am running a series of queries in a stored procedure.
For example, my first query might return a recordset like this
Apples
Oranges
Pears
Turnips
I want to put those reults in variables
So I might have
Declare @.Fruit1 char(10),@.Fruit2 char(10),@.Fruit3 char(10),@.Fruit4
char(10
Select top 4 fruits from tblFruits
How do I get the recordset into the variable?Sorry to answer you with another question but could you explain just
*why* you would want to assign the results to variables? Your reason
may have some bearing on the answer.
Your requirement is a bit unusual. SQL Server doesn't have arrays. The
main data structure is a table and it is hard work to manipulate lists
of variables just because that's not really what the declarative SQL
language was designed to do.
David Portas
SQL Server MVP
--|||Try one by one.
declare @.Fruit1 char(10)
declare @.Fruit2 char(10)
declare @.Fruit3 char(10)
declare @.Fruit4 char(10)
select top 1 @.Fruit1 = fruits from tblFruits
select top 1 @.Fruit2 = fruits from tblFruits
where fruits != @.Fruit1
select top 1 @.Fruit3 = fruits from tblFruits
where fruits != @.Fruit1 and fruits != @.Fruit2
select top 1 @.Fruit4 = fruits from tblFruits
where fruits != @.Fruit1 and fruits != @.Fruit2 and fruits != @.Fruit3
go
AMB
"Bob" wrote:

> Hello folks.
> I am running a series of queries in a stored procedure.
> For example, my first query might return a recordset like this
> Apples
> Oranges
> Pears
> Turnips
> I want to put those reults in variables
> So I might have
> Declare @.Fruit1 char(10),@.Fruit2 char(10),@.Fruit3 char(10),@.Fruit4
> char(10
> Select top 4 fruits from tblFruits
> How do I get the recordset into the variable?
>

Get Minimum day in a continuous series

Hi,

l've a series of day which record the date of an event. l would like to count the # of continuous days for the event. In this case, it would be 14/5, 15/5, 16/5, 17/5, 18/5, 19/5 and 20/5. Any idea to do this in SQL?

Date
--
20/5
19/5
18/5
17/5
16/5
15/5
14/5
09/5
07/5
06/5
05/5
And what about May/5 May/6 and May/7?.. those also meet your request.|||

The only thing that comes to mind is to use a cursor to loop through your rows.

You can then check each row to determine if it is exactly one day beyond the last row.

Do you just want to get a count of the highest number of days? Or what do you want to return?

|||

If you are using SQL Server 2005 you
can do this (this can be modified to work
with SQL Server 2000 if required)

set dateformat dmy
create table #dates(dt datetime)

insert into #dates(dt) values('20/5/2006')
insert into #dates(dt) values('19/5/2006')
insert into #dates(dt) values('18/5/2006')
insert into #dates(dt) values('17/5/2006')
insert into #dates(dt) values('16/5/2006')
insert into #dates(dt) values('15/5/2006')
insert into #dates(dt) values('14/5/2006')
insert into #dates(dt) values('09/5/2006')
insert into #dates(dt) values('07/5/2006')
insert into #dates(dt) values('06/5/2006')
insert into #dates(dt) values('05/5/2006');

with dt_rn(dt,rn)
as
(select dt,
dt-rank() over(order by dt)
from #dates)
select convert(char(5),min(dt),103) as EventStart,
count(*) as ContinuousDays
from dt_rn
group by rn
having count(*)>1

drop table #dates

|||

Hello

To get the Minimum day of each sequence you could try this...

drop table #dates

set dateformat dmy
create table #dates(dt datetime)

insert into #dates(dt) values('20/5/2006')
insert into #dates(dt) values('19/5/2006')
insert into #dates(dt) values('18/5/2006')
insert into #dates(dt) values('17/5/2006')
insert into #dates(dt) values('16/5/2006')
insert into #dates(dt) values('15/5/2006')
insert into #dates(dt) values('14/5/2006')
insert into #dates(dt) values('09/5/2006')
insert into #dates(dt) values('07/5/2006')
insert into #dates(dt) values('06/5/2006')
insert into #dates(dt) values('05/5/2006')

select * from #dates od where not exists (select dt from #dates id where id.dt = od.dt-1)
and exists (select dt from #dates id2 where id2.dt = od.dt+1)

The 2nd Part of the where clause is needed to eliminate the 09/05 date... If you consider that single date a "sequence" also then remove the 2nd part.

Be warned though that this querry can eat up a lot of performance.

|||this query must return the event start date and count of continous event dates. you must to replace date_column and table_name

select start_date, count(event_date) as cnt from (
select date_column as event_date,
(select max(date_column) from (
select date_column
from table_name t1
left outer join table_name t2
on t1.date_column=dateadd(t2,1,date_column)
where t2.date is null
) as start_dates
where start_dates.date_column<=events.date_column
) as start_date
from table_name events
) as event_dates
group by event_start_date