How to convert local time to UTC time in SQL

DECLARE @DIFF INT

SET @DIFF = DATEDIFF(HOUR,GETDATE(),GETUTCDATE())

SELECT @DIFF AS DIFFERENCE_IN_HOUR

SELECT DATEADD(HOUR, @DIFF, GETDATE()) AS CURRENT_UTC_TIME

------------------------------------------------------------

DECLARE @DIFF1 INT

SET @DIFF1 = DATEDIFF(MINUTE,GETDATE(),GETUTCDATE())

SELECT @DIFF1 AS DIFFERENCE_IN_MINUTE

SELECT DATEADD(MINUTE, @DIFF1, GETDATE()) AS CURRENT_UTC_TIME

-------------------------------------------------------------

DECLARE @DIFF2 INT

SET @DIFF2 = DATEDIFF(SECOND,GETDATE(),GETUTCDATE())

SELECT @DIFF2 AS DIFFERENCE_IN_SECONDS

SELECT DATEADD(SECOND, @DIFF2, GETDATE()) AS CURRENT_UTC_TIME

------------------------------------------------------------

DECLARE @DIFF3 INT

SET @DIFF3 = DATEDIFF(DAY,GETDATE(),GETUTCDATE())

SELECT @DIFF3 AS DIFFERENCE_IN_DAYS

SELECT DATEADD(DAY, @DIFF3, GETDATE()) AS CURRENT_UTC_TIME

-----------------------------------------------------------

you may look the sql query result:

Resource:

http://hemant-srivastava.blogspot.com/ 

Swap the values of two columns in SQL Server

 Do you have fun with SQL Server?. Let's start fun with SQL Server. Suppose you want to swap the values of two columns of a table in SQL Server, how could you achieve this tricky task?. Actually, it is simple and so funny.

Suppose you have a Customer table in the database with the following data and you want to interchange the values of columns Name and Address then how do you do?

 SELECT * FROM CUSTOMER 

Don't worry, to do this task, you need to just write a simple update query for Customer table like as :

 UPDATE Customer SET Name=Address , Address=Name 

Now After query execution you will find the the values of columns Name and Address have been interchanged, that's so cool.

 SELECT * FROM CUSTOMER 

Note

1. Data type of both the columns should be same and also length should be enough to hold the swapped column data other wise data will be truncated.

Resources:-

http://www.dotnet-tricks.com/Tutorial/sqlserver/PW8W071112-Swap-the-values-of-two-columns-in-SQL-Server.html

SQL SERVER – 2008 – TRIM() Function – User Defined Function

“How come SQL Server 2000, 2005 does not have function TRIM()? Is there any way to get similar results.

What about SQL Server 2008?”

James has asked very interesting question. I have previously wrote about SQL SERVER – TRIM() Function – UDF TRIM(). Today my answer is no different than what I answered in earlier post.

SQL Server does not have function which can trim leading or trailing spaces of any string at the same time. SQL does have LTRIM() and RTRIM() which can trim leading and trailing spaces respectively. SQL Server 2008 also does not have TRIM() function. User can easily use LTRIM() and RTRIM() together and simulate TRIM() functionality.

SELECT RTRIM(LTRIM(' Word ')) AS Answer;

Should give result set without any leading or trailing spaces.

Answer
——
Word

I have created following UDF which everyday when I have to TRIM() any word or column.
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))RETURNS VARCHAR(MAX)BEGIN
RETURN LTRIM(RTRIM(@string))ENDGO
Now let us test above UDF running following statement where there are leading and trailing spaces around word.
SELECT dbo.TRIM(' leading trailing ')
It will return string in result window as
'leading trailing'
There will be no spaces around them. If extra spaces are useless data, when data is inserted in database they should be trimmed. If there is need of spaces in data but in certain cases they should be trimmed when retrieving we can use Computed Columns. Read more about computed columns SQL SERVER – Puzzle – Solution – Computed Columns Datatype Explanation.

Following example demonstrates how computed columns can be used to retrieve trimmed data.

USE AdventureWorks
GO/* Create Table */CREATE TABLE MyTable(ID TINYINT NOT NULL IDENTITY (1, 1),FirstCol VARCHAR(150) NOT NULL,TrimmedCol AS LTRIM(RTRIM(FirstCol))
) ON [PRIMARY]
GO/* Populated Table */INSERT INTO MyTable([FirstCol])SELECT ' Leading'UNION
SELECT 'Trailing 'UNION
SELECT ' Leading and Trailing 'UNION
SELECT 'NoSpaceAround'GO/* SELECT Table Data */SELECT *FROM MyTable
GO/* Dropping Table */DROP TABLE MyTable
GO

Above query demonstrates that when retrieving data it retrieves trimmed data in column TrimmedCol. You can see the result set in following image.

Computed columns are created run time and performance may not be optimal if lots of data is being retrieved. We will see some other time how we can improve the performance of Computed Column using Index.

Resources:-

http://blog.sqlauthority.com/

SQL Server 2008 Date Format

Standard Date Formats
Date Format	Standard	SQL Statement	Sample Output
Mon DD YYYY 1 HH:MIAM (or PM)	Default	SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100)	Jun 8 2011 1:30PM 1
MM/DD/YY	USA	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY]	06/08/11
MM/DD/YYYY	USA	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]	06/08/2011
YY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD]	11.06.08
YYYY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD]	2011.06.08
DD/MM/YY	British/French	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY]	08/06/11
DD/MM/YYYY	British/French	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY]	08/06/2011
DD.MM.YY	German	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY]	08.06.11
DD.MM.YYYY	German	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]	08.06.2011
DD-MM-YY	Italian	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY]	08-06-11
DD-MM-YYYY	Italian	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY]	08-06-2011
DD Mon YY 1	-	SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY]	08 Jun 11 1
DD Mon YYYY 1	-	SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY]	08 Jun 2011 1
Mon DD, YY 1	-	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY]	Jun 08, 11 1
Mon DD, YYYY 1	-	SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]	Jun 08, 2011 1
HH:MM:SS	-	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 8) SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108)	13:30:45
Mon D YYYY H:MI:SS.NNNNNNNAM (or PM) 1	Default + nanoseconds	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9) SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)	Jun 8 2011 1:30:45.9428675PM 1
MM-DD-YY	USA	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY]	06-08-11
MM-DD-YYYY	USA	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY]	06-08-2011
YY/MM/DD	-	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD]	11/06/08
YYYY/MM/DD	-	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD]	2011/06/08
YYMMDD	ISO	SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD]	110608
YYYYMMDD	ISO	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD]	20110608
DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1	Europe default + nanoseconds	SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 13) SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113)	08 Jun 2011 13:30:45.94286751
HH:MI:SS.NNNNNNN(24H)	-	SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 14) AS [HH:MI:SS:MMM(24H)] SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 114) AS [HH:MI:SS:MMM(24H)]	13:30:45.9428675
YYYY-MM-DD HH:MI:SS(24h)	ODBC Canonical	SELECT CONVERT(VARCHAR(19), SYSDATETIME(), 120)	2011-06-08 13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)	ODBC Canonical (with nanoseconds)	SELECT CONVERT(VARCHAR(23), SYSDATETIME(), 121)	2011-06-08 13:30:45.9428675
MM/DD/YY HH:MI:SS AM	-	SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22)	06/08/11 1:30:45 PM
YYYY-MM-DD	-	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 23)	2011-06-091
HH:MI:SS (24h)	-	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 24)	13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNN	-	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 25)	2011-06-08 13:30:45.94286751
YYYY-MM-DDTHH:MM:SS:NNNNNNN	ISO8601	SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126)	2011-06-08T13:30:45.9428675
DD Mon YYYY HH:MI:SS.NNNNNNNAM 1	Kuwaiti	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130)	08 Jun 2011 1:30:45.9428675PM1
DD/MM/YYYY HH:MI:SS.NNNNNNNAM	Kuwaiti	SELECT CONVERT(VARCHAR(25), SYSDATETIME(), 131)	08/06/2011 1:30:45.9428675PM

Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function.

Extended Date Formats
Date Format	SQL Statement	Sample Output
YY-MM-DD	SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 20), 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 11), '/', '-') AS [YY-MM-DD]	11-06-08
YYYY-MM-DD	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 111), '/', '-') AS [YYYY-MM-DD]	2011-06-08
YYYY-M-D	SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M-D]	2011-6-8
YY-M-D	SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M-D]	11-6-8
M-D-YYYY	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-D-YYYY]	6-8-2011
M-D-YY	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-D-YY]	6-8-11
D-M-YYYY	SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D-M-YYYY]	8-6-2011
D-M-YY	SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D-M-YY]	8-6-11
YY-MM	SELECT RIGHT(CONVERT(VARCHAR(7), SYSDATETIME(), 20), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), SYSDATETIME(), 120), 3, 5) AS [YY-MM]	11-06
YYYY-MM	SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 120) AS [YYYY-MM]	2011-06
YY-M	SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M]	11-6
YYYY-M	SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M]	2011-6
MM-YY	SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 4, 5) AS [MM-YY]	06-11
MM-YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 105), 7) AS [MM-YYYY]	06-2011
M-YY	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-YY]	6-11
M-YYYY	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-YYYY]	6-2011
MM-DD	SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 10) AS [MM-DD]	06-08
DD-MM	SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 5) AS [DD-MM]	08-06
M-D	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M-D]	6-8
D-M	SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D-M]	8-6
M/D/YYYY	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/D/YYYY]	6/8/2011
M/D/YY	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/D/YY]	6/8/11
D/M/YYYY	SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D/M/YYYY]	8/6/2011
D/M/YY	SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D/M/YY]	8/6/11
YYYY/M/D	SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M/D]	2011/6/8
YY/M/D	SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M/D]	11/6/8
MM/YY	SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 3), 5) AS [MM/YY]	06/11
MM/YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 103), 7) AS [MM/YYYY]	06/2011
M/YY	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/YY]	6/11
M/YYYY	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/YYYY]	6/2011
YY/MM	SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 11) AS [YY/MM]	11/06
YYYY/MM	SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 111) AS [YYYY/MM]	2011/06
YY/M	SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M]	11/6
YYYY/M	SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M]	2011/6
MM/DD	SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 1) AS [MM/DD]	06/08
DD/MM	SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 3) AS [DD/MM]	08/06
M/D	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M/D]	6/8
D/M	SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D/M]	8/6
MM.DD.YYYY	SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '.') AS [MM.DD.YYYY]	06.08.2011
MM.DD.YY	SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '.') AS [MM.DD.YY]	06.08.11
M.D.YYYY	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.D.YYYY]	6.8.2011
M.D.YY	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.D.YY]	6.8.11
DD.MM.YYYY	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]	08.06.2011
DD.MM.YY	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 4) AS [DD.MM.YY]	08.06.11
D.M.YYYY	SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D.M.YYYY]	8.6.2011
D.M.YY	SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D.M.YY]	8.6.11
YYYY.M.D	SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M.D]	2011.6.8
YY.M.D	SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M.D]	11.6.8
MM.YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 104), 7) AS [MM.YYYY]	06.2011
MM.YY	SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 4), 5) AS [MM.YY]	06.11
M.YYYY	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.YYYY]	6.2011
M.YY	SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.YY]	6.11
YYYY.MM	SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 102) AS [YYYY.MM]	2011.06
YY.MM	SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 2) AS [YY.MM]	11.06
YYYY.M	SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M]	2011.6
YY.M	SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M]	11.6
MM.DD	SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 2), 5) AS [MM.DD]	06.08
DD.MM	SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 4) AS [DD.MM]	08.06
MMDDYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '') AS [MMDDYYYY]	06082011
MMDDYY	SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '') AS [MMDDYY]	060811
DDMMYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', '') AS [DDMMYYYY]	08062011
DDMMYY	SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') AS [DDMMYY]	080611
MMYYYY	SELECT RIGHT(REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', ''), 6) AS [MMYYYY]	062011
MMYY	SELECT RIGHT(REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', ''), 4) AS [MMYY]	0611
YYYYMM	SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 112) AS [YYYYMM]	201106
YYMM	SELECT CONVERT(VARCHAR(4), SYSDATETIME(), 12) AS [YYMM]	1106
Month DD, YYYY 1	SELECT DATENAME(MONTH, SYSDATETIME())+ ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ', ' + DATENAME(YEAR, SYSDATETIME()) AS [Month DD, YYYY]	June 08, 20111
Mon YYYY 1	SELECT LEFT(DATENAME(MONTH, SYSDATETIME()), 3) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Mon YYYY]	Jun 2011 1
Month YYYY1	SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Month YYYY]	June 2011 1
DD Month 1	SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME()) AS [DD Month]	08 June 1
Month DD 1	SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) AS [Month DD]	June 08 1
DD Month YY 1	SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ' ' + DATENAME(MM, SYSDATETIME()) + ' ' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [DD Month YY]	08 June 11 1
DD Month YYYY 1	SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME())+ ' ' + DATENAME(YEAR, SYSDATETIME()) AS [DD Month YYYY]	08 June 2011 1
Mon-YY 1	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), SYSDATETIME(), 6), 6), ' ', '-') AS [Mon-YY]	Jun-08 1
Mon-YYYY 1	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), SYSDATETIME(), 106), 8), ' ', '-') AS [Mon-YYYY]	Jun-2011 1
DD-Mon-YY1	SELECT REPLACE(CONVERT(VARCHAR(9), SYSDATETIME(), 6), ' ', '-') AS [DD-Mon-YY]	08-Jun-11 1
DD-Mon-YYYY 1	SELECT REPLACE(CONVERT(VARCHAR(11), SYSDATETIME(), 106), ' ', '-') AS [DD-Mon-YYYY]	08-Jun-2011 1

¹ To make the month name in upper case, simply use the UPPER string function.

How to convert a dd/mm/yyyy string to datetime

Whenever you work on legacy data, you usually expect some sort of data in incorrect data types, but of course with the compatibility. Recently, I migrated one of my client’s data from legacy system to SQL Server 2012 and I came across an issue where the client had a date in varchar field and the data had been placed in dd/mm/yyyy format in that field. The data type had to be changed from varchar to datetime. Lets try to convert it from dd/mm/yyyy (varchar) to datetime.

Given below is the script.

DECLARE @Varchar_Date varchar(11) SET @Varchar_Date='31/12/2012' SELECT CONVERT(datetime,@Varchar_Date) --OUTPUT

Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Ooopsss, you cannot convert it directly. I continued with my research. Finally resolved it. Given below is the solution.

Solution :
In this solution, we will use TRY_PARSE function introduced in SQL Server 2012. Given below is the script.

--This script is compatible with SQL Server 2012 and above. DECLARE @Varchar_Date as varchar(11) SET @Varchar_Date='31/12/2013' SELECT TRY_PARSE(@Varchar_Date AS DATETIME USING 'en-GB') As [Result] --OUTPUT

Result
———————–
2013-12-31 00:00:00.000

(1 row(s) affected)

Let me know if you came across this issue and resolved it in a better way.
Note : For the earlier versions of SQL Servers, you can convert it like this.