Orange Coast Database Forums

Post Info TOPIC: SQL Scripts


Directeur

Status: Offline
Posts: 42
Date:
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

__________________
Anonymous

Date:

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)
as
begin
 return convert(varchar,@DateIn,101);
end
go

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



__________________
Anonymous

Date:
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,
 case  
 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 Del.icio.us
Members Login
Username 
 
Password 
    Remember Me  


Create your own FREE Forum
Report Abuse
Powered by ActiveBoard