Solved

SQL date diff year

Posted on 2014-01-19
9
581 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 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

17 Experts available now in Live!

Get 1:1 Help Now