Date Manipulations

There are many ways we can display the date time of SQL Server.

In these examples, I have used SQL Built-In Functions CONVERT and GETDATE.

The SQL CONVERT function is provided to help with converting a DATETIME or SMALLDATETIME variables, or any other string that holds a valid date, into different date/time display formats.


The SQL GETDATE function is used to get us the current date/time.




1. SELECT CONVERT(CHAR(19),GETDATE()) --Returns May 20 2009 11:30 AM

2. SELECT CONVERT(CHAR(8),GETDATE(),10) -- Returns 05-20-09

3. SELECT CONVERT(CHAR(10),GETDATE(),110) -- Returns 05-20-2009

4. SELECT CONVERT(CHAR(9),GETDATE(),6) -- Returns 20 May 09

5. SELECT CONVERT(CHAR(24),GETDATE(),113) -- Returns 20 May 2008 11:31:39:567




The following user defined function is used to convert the date in 12-Hour Format With AM/PM with.

/*It accepts MM-YY-DD Time */ as parameter

/*It returns YYYY-MM-DD HH:MM AM Format*/




CREATE FUNCTION convDateYYYYMMDDAMPM (@date datetime) AS varchar(50)

DECLARE @myDate As varchar(50)


--Print the Input Date

print 'Input Date:' + @date

--Convert the date into 12-Hour Format With AM/PM

SET @myDate = SELECT CAST(DATEPART(YYYY,@date) AS CHAR(4)) + '-'
+ RIGHT(CAST(100+DATEPART(MM,@date) AS CHAR(3)),2) + '-'
+ RIGHT(CAST(100+DATEPART(DD,@date) AS CHAR(3)),2) + ' '
+ CASE WHEN DATEPART(HH,@date) < 13 --Check For 24 hour Format Less than 13
THEN RIGHT(CAST(100+DATEPART(HH,@date) AS CHAR(3)),2)
ELSE CAST(DATEPART(HH,@date)-12 AS CHAR(2))
END + ':'
+ RIGHT(CAST(100+DATEPART(MI,@date) AS CHAR(3)),2)
+ CASE WHEN DATEPART(HH,@date) < 13 --If the hour is less than 13
THEN 'AM'
ELSE 'PM'
END

RETURN @myDate
END





Result:

dbo.convDateYYYYMMDDAMPM('12-01-2009 13:10') will return

2009-12-01 01:10 AM