SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Yesterday date return
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
439 Posts

Posted - 05/01/2013 :  08:53:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 05/01/2013 :  08:58:28  Show Profile  Reply with Quote
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')
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
439 Posts

Posted - 05/01/2013 :  09:05:17  Show Profile  Reply with Quote
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...!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 05/01/2013 :  09:39:21  Show Profile  Reply with Quote
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:
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.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
439 Posts

Posted - 05/01/2013 :  09:47:15  Show Profile  Reply with Quote
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...!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 05/01/2013 :  10:37:20  Show Profile  Reply with Quote
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?
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
439 Posts

Posted - 05/01/2013 :  10:54:14  Show Profile  Reply with Quote
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...!
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/01/2013 :  11:12:05  Show Profile  Reply with Quote
You can do this:



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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/02/2013 :  02:01:21  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30188 Posts

Posted - 05/04/2013 :  13:06:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
439 Posts

Posted - 05/07/2013 :  07:13:26  Show Profile  Reply with Quote
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...!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000