Yesterday date return - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Yesterday date return

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-05-01 : 08:53:32

Hi

I can return yesterdays figure here by adding the actual date (currently commented out). Can the same thing be done without using an actual date string, so can I use DateAdd to achivee the same result?

WHERE C.OPEN_FLAG = 1 AND C.TYPE = 'Incident'
AND EVENT_DT_FK = DATEADD(d,-2,GETDATE())--with DataAdd?
-- 20130429--with date string

Thanks

SZ1
Learning and development is the driving force in the universe...!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-01 : 08:58:28

Yes you can. You would use it as shown below, which would give you April 27, 2013.

DATEADD(d,-2,'20130429')

One thing to be careful about though, is that datetime data types store the time portion as well. So if your EVENT_DT_FK has the time portion also, and assuming you are trying to get the data for a given date (rather than data that matches a specific date and time), you should get rid of the time portion. SO you would do something like this:

CAST(EVENT_DT_FK AS DATE) =  DATEADD(d,-2,'20130429')

That would work, but an even better approach which is logically the same would be

EVENT_DT_FK >= DATEADD(d,-2,'20130429')
AND EVENT_DT_FK >= DATEADD(d,-1,'20130429')

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-05-01 : 09:05:17

I see what you mean here but I need it to automate, hence I added the GetDate()) otherwise I would need to manually add the date string each time DATEADD(d,-2,'20130429')--I would need to change the date string everyday to calculate this but it needs to be automatic?
Thanks

SZ1
Learning and development is the driving force in the universe...!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-01 : 09:39:21

I thought you were asking for the reverse. If you want to get all the data where EVENT_DT_FK is sometime today, do this:

EVENT_DT_FK >= CAST(GETDATE() AS DATE) AND EVENT_DT_FK < CAST(DATEADD(d,1,GETDATE()) AS DATE)

If you want to get all of yesterday's data do this:


[code]EVENT_DT_FK >= CAST(DATEADD(d,-1,GETDATE()) AS DATE)AND EVENT_DT_FK < CAST(GETDATE() AS DATE)

This will be automatic as you described it. That is to say, if you were to run it today (which is May 1st), the first query will give you any row where EVENT_DT_FK is sometime today. IF you were to run the same thing on May 5th, it will give you any row where EVENT_DT_FK is on the 5th.

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-05-01 : 09:47:15

So can I do this for yesterday results?
I get an error of Operand type clash: date is incompatible with int

--trend yesterday, always 2 days behind todays date
SELECT TOP 6 C.INTI_CATEGORY, COUNT(OBJ_ID) AS total FROM
AGG_DAY_LIFECYCLE FD
INNER JOIN DIM_CALL C ON FD.INC_KEY = C.[KEY]
WHERE C.OPEN_FLAG = 1 AND C.TYPE = 'Incident'
--AND EVENT_DT_FK = DATEADD(d,-2,GETDATE())-- 20130429
And EVENT_DT_FK >= CAST(DATEADD(d,-2,GETDATE()) AS DATE)
And EVENT_DT_FK < CAST(GETDATE() AS DATE)
GROUP BY C.INTI_CATEGORY
ORDER BY total DESC

SZ1
Learning and development is the driving force in the universe...!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-01 : 10:37:20

That will get you two days data. Today being May 1st, that will get you data for April 29th and April 30th. If you just want April 30th data, change the -2 to -1 as in


        AND EVENT_DT_FK >= CAST(DATEADD(d, -1, GETDATE()) AS DATE)
        AND EVENT_DT_FK < CAST(GETDATE() AS DATE)

The error that you are getting is probably because EVENT_DT_FK is integer type. See if that is the case using this query

SELECT TABLE_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'EVENT_DT_FK'

If it is an integer, then how do we interpret that?

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-05-01 : 10:54:14

yeah coming back as int it returns format like this: 20130130, can we datepart it then convert to date.

SZ1
Learning and development is the driving force in the universe...!

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-01 : 11:12:05

You can do this:

[CODE]

SELECT CAST(CAST(20130130 AS VARCHAR) AS DATE);

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-02 : 02:01:21

quote:
Originally posted by sz1

yeah coming back as int it returns format like this: 20130130, can we datepart it then convert to date.

SZ1
Learning and development is the driving force in the universe...!

you should use proper datatype for your fields
If its a date that you're storing datatype should be datetime or date. Otherwise you've to do a lot of unnecessary convert operations.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-05-04 : 13:06:03

Stop the madness. Please use existing indexes if possible and present.

DECLARE	@FromDate INT = CONVERT(CHAR(8), DATEADD(DAY, -1, GETDATE()), 112),
	@ToDate INT = CONVERT(CHAR(8), DATEADD(DAY, 1, GETDATE()), 112);

--trend yesterday, always 2 days behind todays date
SELECT TOP(6)	C.INTI_CATEGORY,
		COUNT(FD.OBJ_ID) AS total
FROM		dbo.AGG_DAY_LIFECYCLE As FD
INNER JOIN	dbo.DIM_CALL AS C ON C.[KEY] = FD.INC_KEY
			AND C.OPEN_FLAG = 1
			AND C.TYPE = 'Incident'
WHERE		FD.EVENT_DT_FK >= @FromDate
		AND FD.EVENT_DT_FK < @ToDate
GROUP BY	C.INTI_CATEGORY
ORDER BY	COUNT(FD.OBJ_ID) DESC;

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

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-05-07 : 07:13:26

I didn't create the db for the reporting I'm just the glug trying to create some reports from it...al try this suggestion and get back...
Thanks

SZ1
to learn is to show the universe that you care...!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources