October 23, 2015 at 11:03 am
Dumb question.
What is wrong with my syntax?
I want to return the value of the AchiveYear Value based on records in theCall that match.
SELECT DATEPART(yyyy,Call_Date) AS ArchiverYear
FROM tblCall
INNER JOIN PrismDataArchive.dbo.ArchiveDriver AS Arch ON tblCall.DATEPART(yyyy,Call_Date) = Arch.ArchiveYear
ArchiveDriver Values
IDTableName Active_IndicatorBatchSizeArchiveYear
1tblCall 1 5000 1899
2xactControlPoint 1 50001990
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 23, 2015 at 11:09 am
Run this code and see if anything comes back:
SELECT DATEPART(yyyy,Call_Date) AS ArchiverYear
FROM tblCall
WHERE Call_Date <= '01/01/1900'
If not, there are no records in the table with a call date in the year 1899
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 23, 2015 at 11:11 am
Michael L John (10/23/2015)
Run this code and see if anything comes back:SELECT DATEPART(yyyy,Call_Date) AS ArchiverYear
FROM tblCall
WHERE Call_Date <= '01/01/1900'
If not, there are no records in the table with a call date in the year 1899
Thanks.
This is what I'm trying to do.
I should have provided you with additional information.
Thanks again.
[
BEGIN
DELETE TOP (@BatchSize) FROM tc
OUTPUT DELETED.* INTO PrismDataArchive.dbo.tblCallArchive
FROM PrismData.[dbo].[tblCall] tc
WHERE DATEPART(yyyy,tc.Call_Date) = @ArchiveYear
--WHERE tc.Call_Date >= DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0) --jan 1 of the year of the date param passed.
--AND tc.Call_Date < DATEADD(yy,1,DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0)) --add one year:
END
/code]
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 23, 2015 at 11:13 am
Michael L John (10/23/2015)
Run this code and see if anything comes back:SELECT DATEPART(yyyy,Call_Date) AS ArchiverYear
FROM tblCall
WHERE Call_Date <= '01/01/1900'
If not, there are no records in the table with a call date in the year 1899
I can't hard code the year. It has to get the year from the Table and then use that logic in the WHERE Clause.
I need to delete all of the records for the year that is stored in the Driver Table.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 23, 2015 at 11:20 am
I'm not asking you to hard-code the year.
I asking you to query the table and see if any records exist in the table that match the criteria.
If there are records in the table for the year 1899, then the syntax is incorrect in some way.
If no records exist in the table, then your logic is incorrect.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 23, 2015 at 11:53 am
This:
tblCall.DATEPART(yyyy,Call_Date)
Should be this:
DATEPART(yyyy,tblCall.Call_Date)
You qualify the column, not the function.
October 23, 2015 at 11:55 am
You're trying to call a UDF DATEPART defined under the tblCall schema. Unless you've defined your own DATEPART function under this schema, I suspect what you want is DATEPART(yyyy,tblCall.Call_Date) instead of tblCall.DATEPART(yyyy,Call_Date).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 23, 2015 at 12:03 pm
drew.allen (10/23/2015)
You're trying to call a UDF DATEPART defined under the tblCall schema. Unless you've defined your own DATEPART function under this schema, I suspect what you want is DATEPART(yyyy,tblCall.Call_Date) instead of tblCall.DATEPART(yyyy,Call_Date).Drew
Hey Drew,
I believe that tblCall is a table and DATEPART references the system function (especially since yyyy has no quotes).
October 23, 2015 at 12:13 pm
drew.allen (10/23/2015)
You're trying to call a UDF DATEPART defined under the tblCall schema. Unless you've defined your own DATEPART function under this schema, I suspect what you want is DATEPART(yyyy,tblCall.Call_Date) instead of tblCall.DATEPART(yyyy,Call_Date).Drew
Better eyes than mine!!!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 23, 2015 at 12:23 pm
Michael L John (10/23/2015)
I'm not asking you to hard-code the year.I asking you to query the table and see if any records exist in the table that match the criteria.
If there are records in the table for the year 1899, then the syntax is incorrect in some way.
If no records exist in the table, then your logic is incorrect.
Call_DateRecordCount
NULL46
189930
19951
19981295
19997967
20006600
200150483
200267137
200370068
200467311
200568502
200668595
200784438
2008104377
2009151171
2010200165
2011232679
2012242464
2013285255
2014355891
2015295637
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 23, 2015 at 12:28 pm
Welsh Corgi (10/23/2015)
Michael L John (10/23/2015)
I'm not asking you to hard-code the year.I asking you to query the table and see if any records exist in the table that match the criteria.
If there are records in the table for the year 1899, then the syntax is incorrect in some way.
If no records exist in the table, then your logic is incorrect.
Call_DateRecordCount
NULL46
189930
19951
19981295
19997967
20006600
200150483
200267137
200370068
200467311
200568502
200668595
200784438
2008104377
2009151171
2010200165
2011232679
2012242464
2013285255
2014355891
2015295637
I don't know what data you're working with, but given the large gap between 1899 and 1995, I think I would question the validity of the year 1899. It could be right, but I don't know you data.
If you join the way I think you want to join, then you're going to be left with a non-SARGable join condition.
October 23, 2015 at 1:12 pm
Something like this?
SELECT
DATEPART(yyyy,tc.Call_Date) AS ArchiverYear
FROM
dbo.tblCall tc
INNER JOIN PrismDataArchive.dbo.ArchiveDriver AS Arch
ON Arch.ArchiveYear = DATEPART(yyyy,tc.Call_Date);
October 23, 2015 at 1:29 pm
Lynn Pettis (10/23/2015)
Something like this?
SELECT
DATEPART(yyyy,tc.Call_Date) AS ArchiverYear
FROM
dbo.tblCall tc
INNER JOIN PrismDataArchive.dbo.ArchiveDriver AS Arch
ON Arch.ArchiveYear = DATEPART(yyyy,tc.Call_Date);
That worked perfectly Lynn.
Thanks.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 23, 2015 at 1:47 pm
Luis Cazares (10/23/2015)
drew.allen (10/23/2015)
You're trying to call a UDF DATEPART defined under the tblCall schema. Unless you've defined your own DATEPART function under this schema, I suspect what you want is DATEPART(yyyy,tblCall.Call_Date) instead of tblCall.DATEPART(yyyy,Call_Date).Drew
Hey Drew,
I believe that tblCall is a table and DATEPART references the system function (especially since yyyy has no quotes).
I agree that is most likely what he was trying to do. I was merely pointing out what he was actually doing. I suspect what he thought he was doing was calling the DATEPART method from the tblCall object, but, of course, SQL tables don't support methods.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 24, 2015 at 9:56 am
Why use DATEPART(), when you can use YEAR() for this?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply