Current_timestamp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Current_timestamp

Author

Topic

Fredforfree
Starting Member

49 Posts

Posted - 2009-06-10 : 13:47:04

I have a report generated every day.
I am attempting to select * from table
WHERE Year(datefield) = Year(CURRENT_TIMESTAMP)
and month(datefield) = month(CURRENT_TIMESTAMP)

problem is i need current_timestamp or todays date to = -1 yesterday
because when month changes over then I am missing the last day of previus month

Please help

Fred

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-10 : 14:04:47

do you mean this?


select * from table 
WHERE datefield >= DATEADD(mm,DATEDIFF(mm,0,CURRENT_TIMESTAMP-1),0)
and datefield < DATEADD(mm,DATEDIFF(mm,0,CURRENT_TIMESTAMP-1)+1,0)

Fredforfree
Starting Member

49 Posts

Posted - 2009-06-10 : 14:13:56

so your select statment will return June 1-30 on July 1st?

quote:

Originally posted by visakh16

do you mean this?


select * from table 
WHERE datefield >= DATEADD(mm,DATEDIFF(mm,0,CURRENT_TIMESTAMP-1),0)
and datefield < DATEADD(mm,DATEDIFF(mm,0,CURRENT_TIMESTAMP-1)+1,0)

Fred

Fredforfree
Starting Member

49 Posts

Posted - 2009-06-10 : 14:15:02

I think so, so your select statment will return all data for June on July 1st?

Fred

Fred

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-10 : 14:18:01

quote:
Originally posted by Fredforfree

I think so, so your select statment will return all data for June on July 1st?

Fred

Fred

yup... did you try it?

Fredforfree
Starting Member

49 Posts

Posted - 2009-06-10 : 14:22:24

yes thanks it returned all data for June 1-9th same as my original code but because the reporting services is scheduled to generate report at 9:00 AM after data is loaded for previous day my code would always miss the last day of month.

Thanks

Fred

Fred

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-10 : 14:24:34

quote:
Originally posted by Fredforfree

yes thanks it returned all data for June 1-9th same as my original code but because the reporting services is scheduled to generate report at 9:00 AM after data is loaded for previous day my code would always miss the last day of month.

Thanks

Fred

Fred

how would it miss last day if report is scheduled after data loading? can you explain?

Fredforfree
Starting Member

49 Posts

Posted - 2009-06-10 : 14:43:06

I need to report totals for the month each day.
New data for previous day June 30th is not loaded until next day July 1st at 7:00 Am

On July 1st my original select query would return data for current month July but what I need on July 1st is Month to date data for June 1-30

Hope I am explaining myself ok

Fred

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 10:58:32

quote:
Originally posted by Fredforfree

I need to report totals for the month each day.
New data for previous day June 30th is not loaded until next day July 1st at 7:00 Am

On July 1st my original select query would return data for current month July but what I need on July 1st is Month to date data for June 1-30

Hope I am explaining myself ok

Fred

Ok.. and when will report be run on july 1st? before or after 7 am?

Fredforfree
Starting Member

49 Posts

Posted - 2009-06-11 : 14:42:11

I it scheduled for run around 9 AM

quote:
Originally posted by visakh16

quote:
Originally posted by Fredforfree

I need to report totals for the month each day.
New data for previous day June 30th is not loaded until next day July 1st at 7:00 Am

On July 1st my original select query would return data for current month July but what I need on July 1st is Month to date data for June 1-30

Hope I am explaining myself ok

Fred

Ok.. and when will report be run on july 1st? before or after 7 am?

Fred

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-06-11 : 14:56:40

[code]
declare @yesterday datetime

set @yesterday = dateadd(dd,datediff(dd,0,current_timestamp)-1,0)

select
*
from
MyTable
where
-- Greater than or equal to start of month (relative to yesterday)
datefield >= dateadd(mm,datediff(mm,0,@yesterday),0) and
-- Less than start of next month (relative to yesterday)
datefield < dateadd(mm,datediff(mm,0,@yesterday)+1,0)

[/code]

CODO ERGO SUM

Fredforfree
Starting Member

49 Posts

Posted - 2009-06-12 : 07:34:26

Thank you Michael

Fred

Fredforfree
Starting Member

49 Posts

Posted - 2009-06-15 : 13:45:40

Hello again

Declare can't be used in view I have found, this will not work.

quote:

Originally posted by Michael Valentine Jones


declare @yesterday	datetime

set @yesterday = dateadd(dd,datediff(dd,0,current_timestamp)-1,0)

select
	*
from
	MyTable 
where
	-- Greater than or equal to start of month (relative to yesterday)
	datefield >= dateadd(mm,datediff(mm,0,@yesterday),0)	and
	-- Less than start of next month (relative to yesterday)
	datefield  < dateadd(mm,datediff(mm,0,@yesterday)+1,0)

CODO ERGO SUM

Fred

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-15 : 13:54:19

the substitute it in select:-


select
	*
from
	MyTable 
where
	-- Greater than or equal to start of month (relative to yesterday)
	datefield >= dateadd(mm,datediff(mm,0,dateadd(dd,datediff(dd,0,current_timestamp)-1,0)),0)	and
	-- Less than start of next month (relative to yesterday)
	datefield  < dateadd(mm,datediff(mm,0,dateadd(dd,datediff(dd,0,current_timestamp)-1,0))+1,0)

Fredforfree
Starting Member

49 Posts

Posted - 2009-06-16 : 08:19:16

How can I call something like this later from Reporting services

declare @yesterday datetime

set @yesterday = dateadd(dd,datediff(dd,0,current_timestamp)-1,0)

select * from view_eis_isn_time
where start = @yesterday

Fred

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 09:01:27

[code]DECLARE @Today DATETIME,
@Yesterday DATETIME

SELECT @Today = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP),
@Yesterday = DATEDIFF(DAY, 1, CURRENT_TIMESTAMP)

SELECT *
FROM view_eis_isn_time
WHERE start >= @yesterday
AND start < @today[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-16 : 12:30:10

quote:
Originally posted by Fredforfree

How can I call something like this later from Reporting services

declare @yesterday datetime

set @yesterday = dateadd(dd,datediff(dd,0,current_timestamp)-1,0)

select * from view_eis_isn_time
where start = @yesterday

Fred

why dont you wrap this in a procedure and then call procedure from reporting services.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources