DATEDIFF two dates 1 year apart

For a friend who needed to know how to check if two dates in Sybase ASE 12 are a year apart.

A JavaScript Solution

Add moment.js lib

// TODO: Validate if date diff is no more than 1 year
var startSepDt = moment($('#startDateSep').val());
var endSepDt = moment($('#sendDateSep').val());
var dateDiff = endSepDt.diff(startSepDt, 'year');
if (dateDiff > 1) 
{
       showInValidSeasrchWarning('The date range cannot exceed a year.');
       return false;
}

 

A SQL Solution

-- DATEDIFF two dates 1 year apart
DECLARE @year1 datetime, @year2 datetime
SET @year1 = DATEADD(year,-1,GetDate())
SET @year2 = GetDate()
SELeCT @year1, @year2, datediff(year,@year1,@year2) 
if( datediff(year,@year1,@year2) <= 1 AND datediff(dd,@year1, @year2) <= 365 )
print 'Hurray! Within one year..'
else
print ':( Difference is more than a year'

Dynamic Search Conditions in T‑SQL

I hate dynamic querys as they are not optimized and a query plan cannot be cached….

Example:

Sql = some dyanamic sql string built via if logic…

Exec(sql) – will not cache an query execution plan.

In MS Sql   sp_executesql caches query plans – very cool…

Really though, try to avoid them if possible….

 

Great read on the details…

Newest version of document:  http://www.sommarskog.se/dyn-search-2008.html

Older version

http://www.sommarskog.se/dyn-search-2005.html – Version for SQL 2005 and Earlier

 

Use DMVs to determine based on past executions which indexes would be beneficial in that database.

The following SQL should be run in Production SQL server (off hours). It basically uses DMVs to determine based on past executions which indexes would be beneficial in that database. It recommends what indexes could be created on the specified tables.

SELECT
 migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
 ,
 'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
 + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
 + ' ON ' + mid.statement
 + ' (' + ISNULL (mid.equality_columns,'')
 + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
 + ISNULL (mid.inequality_columns, '')
 + ')'
 + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
--, migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC