Solved

SQL date diff year

Posted on 2014-01-19
9
599 Views
Last Modified: 2014-01-20
I need to create an anniversary report. This is the original code;

select cs.id as csid, csi.Search_Number as searchID, csi.date_of_placement as datePlaced, ui.firstname, ui.lastname, cp.name as companyname, cp.address_line_1, cp.address_line_2, 
            cp.city, cp.state, cp.zip, cp2.name as currentcompanyname, cp2.address_line_1 as current_address_line_1, cp2.address_line_2 as current_address_line_2, 
            cp2.city as current_city, cp2.state as current_state, cp2.zip as current_zip

        from client_searches_individuals as csi
            left join users_info as ui on ui.id = csi.individual_number
            left join client_searches as cs on cs.id = csi.search_number
            left join companies as Cp on cp.id = cs.company_number
            left join companies as cp2 on cp2.id = ui.current_company_number
        where 0=0 
            <!--- This is for the varchar date field, take away anything with the year selected --->
                and csi.date_of_placement not like '#dateformat(attributes.startdate,'yyyy')#%'

            <cfif attributes.startdate neq "">
                and right(csi.date_of_placement,5) >= '#dateformat(attributes.startdate,'mm/dd')#'
            </cfif>
            <cfif attributes.enddate neq "">
                and right(csi.date_of_placement,5) <= '#dateformat(attributes.enddate,'mm/dd')#'
            </cfif>
            and isnull(cs.id,0) > 0
        order by date_of_placement

Open in new window

It's poor code because running it as of 2013/12/31 will give me anniversaries for years not = 2013 and includes rows from 2014.

I've tried using datediff but I'm getting invalid date errors for null or empty date_of_placement (varchar) column.

All I really need is any record that has a placement date older than one year based on today's date.

using the following code;

where 0=0 
            and (DATEDIFF(year, cast(csi.date_of_placement as datetime), GETDATE() )) => 1
            and isnull(cs.id,0) > 0
            and isnull(date_of_placement,'') > ''

Open in new window


I get; Msg 242, Level 16, State 3, Line 2 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

SQL 2005 CF 9
0
Comment
Question by:lanterv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39793196
It can be a data problem. Please check the values of the column

csi.date_of_placement
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39793197
If you don't want the ones that are null then:

where 0=0 
            and (DATEDIFF(year, cast(IsNull(csi.date_of_placement,'2000-01-01') as datetime), GETDATE() )) => 1
            and isnull(cs.id,0) > 0
            and isnull(date_of_placement,'') > ''

Open in new window

Otherwise:
where 0=0 
            and (DATEDIFF(year, cast(IsNull(csi.date_of_placement,GETDATE()) as datetime), GETDATE() )) => 1
            and isnull(cs.id,0) > 0
            and isnull(date_of_placement,'') > ''

Open in new window


But it looks like you have columns that are dates as text.
0
 

Author Comment

by:lanterv
ID: 39793287
There, indeed were data errors.  I corrected them.  I ran a pass to detect invalid dates.

The code now reads;

<cfquery name="placedIndGoodDates" datasource="#request.dsn#">
		select cs.id as csid, csi.Search_Number as searchID, isnull(csi.date_of_placement,'') as datePlaced, ui.firstname, ui.lastname, cp.name as companyname, cp.address_line_1, cp.address_line_2, 
			cp.city, cp.state, cp.zip, cp2.name as currentcompanyname, cp2.address_line_1 as current_address_line_1, cp2.address_line_2 as current_address_line_2, 
			cp2.city as current_city, cp2.state as current_state, cp2.zip as current_zip
			 
		from client_searches_individuals as csi
			inner join users_info as ui on ui.id = csi.individual_number
			inner join client_searches as cs on cs.id = csi.search_number
			inner join companies as Cp on cp.id = cs.company_number
			inner join companies as cp2 on cp2.id = ui.current_company_number
		where 0=0 
			and isnull(cs.id,0) > 0
			and isnull(csi.date_of_placement,'') > ''
		order by date_of_placement
	</cfquery>
	<!--- <cfdump var="#placedIndGoodDates#"><cfabort> --->
	<cfquery name="placedInd" dbtype="query">
		select *
		from placedIndGoodDates
		where 0=0 
				and DATEDIFF(year, dateplaced, getdate()) > 0
		order by datePlaced
	</cfquery>

Open in new window



Now I get;

Query Of Queries syntax error.
Encountered "DATEDIFF ( year. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,


And the query is;

select * from placedIndGoodDates where 0=0 and DATEDIFF(year, dateplaced, getdate()) > 0 order by datePlaced
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:Jim P.
ID: 39793334
And you generally don't need the where 0=0  take out the  0=0 and and you should be good.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39794305
From a SQL Server standpoint, the syntax is good.  But the WHERE conditions can cleaned up for performance, as follows:


            select cs.id as csid, csi.Search_Number as searchID, isnull(csi.date_of_placement,'') as datePlaced, ui.firstname, ui.lastname, cp.name as companyname, cp.address_line_1, cp.address_line_2,
                  cp.city, cp.state, cp.zip, cp2.name as currentcompanyname, cp2.address_line_1 as current_address_line_1, cp2.address_line_2 as current_address_line_2,
                  cp2.city as current_city, cp2.state as current_state, cp2.zip as current_zip
                  
            from client_searches_individuals as csi
                  inner join users_info as ui on ui.id = csi.individual_number
                  inner join client_searches as cs on cs.id = csi.search_number
                  inner join companies as Cp on cp.id = cs.company_number
                  inner join companies as cp2 on cp2.id = ui.current_company_number
            where 0=0
                  and cs.id > 0
                  and csi.date_of_placement > ''

            order by date_of_placement
            select *
            from placedIndGoodDates
            where 0=0
                        and dateplaced >= DATEADD(year, -1, getdate())
            order by datePlaced
0
 

Author Comment

by:lanterv
ID: 39795015
I wish I had time to figure out why using dateadd works and datediff doesn't.  But it worked and that's a good thing.
0
 

Author Closing Comment

by:lanterv
ID: 39795018
Thank you
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39795187
DATEDIFF() actually checks how many year-end boundaries were crossed, i.e. from Dec 31 to Jan 1, not whether or not a full year elapsed between the dates.

For example:

SELECT DATEDIFF(YEAR, '20120101', '20121231') --full 366 days, but no boundary crossed
SELECT DATEDIFF(YEAR, '20131231', '20140101') --only 1 or 2 days, but one boundary crossed

Btw, DATEDIFF always work that way in SQL.  Thus, DATEDIFF(DAY, ...) counts the number of boundaries from 11:59pm to midnight that are crossed, not 24-hour periods.
0
 

Author Comment

by:lanterv
ID: 39795335
Great info. Thank you.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question