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.
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;
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;
