?
Solved

SQL date diff year

Posted on 2014-01-19
9
Medium Priority
?
603 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 11

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
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 
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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

762 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