Category: SQL Server 2012

in this article will learn how to convert date :

• 1- From AD to Hegira
• 2- From Hegira to AD

AD = “01/01/2011”   & Hegira  = “01/01/1432”

1. Convert Date From AD To Hegira

EXAMPLE NO 1

set dateformat dmy
DECLARE @DATEM DATETIME
DECLARE @ResultH NCHAR(10)
SET @DATEM = ’19/03/2011′
SET @ResultH = (SELECT CONVERT(NCHAR(10), @DATEM, 131))
SELECT @ResultH
PRINT ‘The Date Time IS’ + ‘ ‘ + CAST(@ResultH AS NVARCHAR(50))
PRINT ‘————————————-‘

—————————————————————————————————–

EXAMPLE NO 2

Create Table Convert_Date
(ID int,
[date] datetime)

—- data type of [date] column must be datetime but if you will insert Hegira date the column data type must be Nvarchar
GO
Insert  into Convert_Date values (3,’01/01/2011′),(3,’10/09/2011′)
GO
SET DATEFORMAT DMY
SELECT   ID,[DATE] ,CONVERT(NCHAR(10),CAST([DATE] AS DATETIME ) ,131)
AS  Hegira FROM Convert_Date

—————————————————————————————————-

2-Convert Date From Hegira  to AD

Example No 1
DECLARE @ResultM DATETIME
DECLARE @DateH NVARCHAR(50)
SET @DATEH = ’09/04/1405′
SET @ResultM = (SELECT CONVERT(DATETIME, @dateH, 130))
SELECT CONVERT(NCHAR(10),@ResultM,103)
PRINT ‘The Date Time IS ‘ + ‘ ‘ + CAST(@ResultM AS NVARCHAR(50))
PRINT ‘————————————-‘

—This Script Will return your agg in (Y-M-D)

Set Dateformat DMY

GO

DECLARE @date datetime,
@tmpdate datetime,
@years int, @months int,
@days int SELECT @date = ’01/10/1985′
SELECT @tmpdate = @date SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) – CASE
WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date)
= MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE()))
THEN 1 ELSE 0 END SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) – CASE WHEN DAY(@date) > DAY(GETDATE())
THEN 1 ELSE 0 END SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE()) SELECT @years, @months, @days
—Use the following formula to calculate a person’s age in years
DECLARE @age int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)

SELECT @dob = ’01/10/1985′
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)

–Notes:
–Substitute “getdate()” for the @yyyymmdd variable if you want to determine the person’s age right now.
–The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer.

select datediff (year, convert (datetime, ’01/10/1985′), getdate())

DECLARE @BirthDate DATETIME
DECLARE @CurrentDate DATETIME

SELECT @CurrentDate = ’02/09/2011′, @BirthDate = ’01/10/1985′

SELECT
DATEDIFF(YY, @BirthDate, @CurrentDate) –
CASE
WHEN(
(MONTH(@BirthDate)*100 + DAY(@BirthDate)) >
(MONTH(@CurrentDate)*100 + DAY(@CurrentDate))
) THEN 1
ELSE 0
END

How to Generate Zero without using Any Numbers in T-SQL

select count(*) where ‘a’=’b’
select count(*)-count(*)
select count(cast(null as int))
select Ascii(‘Y’)-Ascii(‘Y’)