Solved

SQL date diff year

Posted on 2014-01-19
9
587 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
  • 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now