Solved

SQL date diff year

Posted on 2014-01-19
9
594 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to count the days a record spends in a step 21 51
SSMS Opening Mode 9 20
Dynamic SQL select query 4 38
Use column to search string column 2 11
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
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 to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 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