Orange Coast Database Forums

Post Info TOPIC: SQL Scripts


Status: Offline
Posts: 42
SQL Scripts

Please post here working SQL scripts you think would benefit our community. This is intended as sort of an examples archive. Post questions and general discussions under SQL.

-- Edited by danhdurso at 06:12, 2008-01-12



Below is a script of a simple function to to remove the time from the display of a datetime field in SQL Server. If you are trying to do this without a function replace @datein with the name of the desired datetime field.

/* create the function
uses the format code 101. There are many of these - see help.
create function DateOnly
(@DateIn datetime)
returns varchar(10)
 return convert(varchar,@DateIn,101);

/* call the date function; using dbo required.*/
select name, dbo.DateOnly(hire_date) as [Hire Date]
from employees;


SQL Script to calculate age

Below is a script that will calculate the age given a birthdate...

/* script to calculate age (best to put in a function)
shows years using datediff then true age which is adjusted depending upon
whether or not the birthday has occured in the current year
use employeedb;
select soc_sec_no, birthdate, datediff(yyyy, birthdate, getdate()) as years,
 when str(datepart(mm,birthdate),2) + str(datepart(d,birthdate),2)
  >= str(datepart(m,getdate()),2) + str(datepart(d,getdate()),2)
 then datediff(yyyy, birthdate, getdate())-1
 else datediff(yyyy, birthdate, getdate())
 end as age
from tblemployees;

Page 1 of 1  sorted by
Quick Reply

Please log in to post quick replies.

Tweet this page Post to Digg Post to
Members Login
    Remember Me  

Create your own FREE Forum
Report Abuse
Powered by ActiveBoard