getdate

5 Jan 2006 4:48 PM

Steven Scaife

Hi

I have to create a series of reports that look back over certain days ie.

day before
current week
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

5 Jan 2006 5:04 PM

Ryan

Have a look at the DATEDIFF function is SQL Books

--
HTH. Ryan

Show quote

"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 week
> 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
>
>

5 Jan 2006 5:19 PM

Alexander Kuznetsov

Also you can use a calendar table

5 Jan 2006 5:16 PM

Absar Ahmad

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)

Show quote

"Steven Scaife" wrote:

> Hi
>
> I have to create a series of reports that look back over certain days ie.
>
> day before
> current week
> 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
>
>
>

5 Jan 2006 5:16 PM

Mark Williams

For yesterday's results, try

WHERE DATEDIFF(dd, dbo.vw_MIS_AppWritten.Date_App_Written, GETDATE()) = 1

For last week'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

--

Show quote

"Steven Scaife" wrote:

> Hi
>
> I have to create a series of reports that look back over certain days ie.
>
> day before
> current week
> 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
>
>
>

5 Jan 2006 5:20 PM

Ryan Powers

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

Show quote

"Steven Scaife" wrote:

> Hi
>
> I have to create a series of reports that look back over certain days ie.
>
> day before
> current week
> 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
>
>
>

5 Jan 2006 5:31 PM

Mark Williams

This works well too

SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

Returns today's date at 00:00:00.000

--

Show quote

"Ryan Powers" wrote:

> 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 week
> > 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
> >
> >
> >

5 Jan 2006 7:45 PM

Ryan Powers

Nice. Thanks Mark.

That is a little cleaner that what I proposed. Good to know.

--
Ryan Powers
Clarity Consulting
http://www.claritycon.com

Show quote

"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:
>
> > 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 week
> > > 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
> > >
> > >
> > >

6 Jan 2006 12:42 PM

Steven Scaife

thank you it is much appreciated

Show quote

"Mark Williams" <MarkWilli***@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:
>
>> 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 week
>> > 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
>> >
>> >
>> >