Feeds:
Posts
Comments

Posts Tagged ‘Format Function’

SQL SERVER – Multiple ways to remove seconds & Milliseconds from Datetime (Truncate Datetime till minute)

Posted in Solutions, SQL SERVER, tagged , , , , on September 18, 2013| 5 Comments »

In my earlier article, I wrote a solution how to remove milliseconds from datetime. After that I tried to remove the seconds and milliseconds from datetime using same method but it gave me errors. So I went to the drawing board and started doing some research and finally realized that I can use the same methods with little modifications.
Given below are the two methods that we can use to remove milliseconds and seconds from datetime.

METHOD 1 :
In this method, we will use Convert function to convert date time to varchar and then remove the seconds and milliseconds from it and then convert it back to datetime.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT
@datetime AS [Datetime with seconds and millisconds]
,CONVERT(DATETIME,CONVERT(VARCHAR(13),@datetime,120)+ ':00')
AS [Datetime without seconds & millisconds]
GO
--OUTPUT

remove seconds and milliseconds.1.1

METHOD 2 :
In this method, we will first FORMAT the datetime value to a defined format (truncate seconds and milliseconds) and then convert it back to datetime data type using CAST function.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT
@datetime AS [Datetime with seconds and millisconds]
,CAST(FORMAT(@datetime,'yyyy-MM-dd HH:0') AS datetime)
AS [Datetime without seconds & millisconds]
GO
--OUTPUT

remove seconds and milliseconds.1.2

Read Full Post »

SQL SERVER 2012- How to format phone numbers

Posted in Solutions, SQL SERVER, tagged , , , , , on August 30, 2013| 1 Comment »

Formatting phone number has never been an issue, the only thing that you need to do is, use few string functions and you can convert it into any format. But what encourages me to write this article is that we can format the phone numbers in any format using only one function, i.e. Format (introduced in SQL Server 2012).

Note : I usually recommend that phone number must be properly maintained inside the database. But sometimes we come across this issue in legacy systems.

Sample :
Let me create a sample to explain it.

USE tempdb
GO
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
[ID] INT,
[Phone Numbers] INT
)
GO
INSERT INTO tbl_sample VALUES (1,3333333333)
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

ID Phone Numbers
———– ————-
1 3333333333

(1 row(s) affected)

SOLUTION :
In the solution, we will use FORMAT function and provide a custom format of telephone number such that,  the Format function will automatically convert it into custom format.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT
[Phone Numbers]
,FORMAT([Phone Numbers],'###-###-####') AS [Formatted Phone]
FROM tbl_sample
UNION ALL
SELECT
[Phone Numbers]
,FORMAT([Phone Numbers],'(###) ###-####') AS [Formatted Phone]
FROM tbl_sample
GO
--OUTPUT

Format_phone_number.1.1

Read Full Post »

SQL SERVER – How to add st, nd, rd & th to dates

Posted in Solutions, SQL SERVER, tagged , , , , , on August 17, 2013| 4 Comments »

Sometimes, date formatting becomes much critical when you know that the format is not supported by the application but to gain the client’s satisfaction, you need to format as per the requirement. I recently came across a date format where I had to add st, nd, rd & th to the dates. Fortunately my client is using SQL Server 2012. So I thought of developing the solution using new functions introduced in SQL Server 2012 to reduce the code and improve the performance.
Let me create a sample to demonstrate the solution.

USE [tempdb]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
--Create a sample table
CREATE TABLE [dbo].[tbl_sample](
[ID] [int] NULL,
[Date] [date] NULL
)
GO
--Insert records in the table
INSERT INTO dbo.[tbl_sample]
SELECT 1 AS [ID] ,N'2013-08-01' AS [Date] UNION ALL
SELECT 2 AS [ID] ,N'2013-08-02' AS [Date] UNION ALL
SELECT 3 AS [ID] ,N'2013-08-03' AS [Date] UNION ALL
SELECT 4 AS [ID] ,N'2013-08-04' AS [Date] UNION ALL
SELECT 5 AS [ID] ,N'2013-08-05' AS [Date] UNION ALL
SELECT 6 AS [ID] ,N'2013-08-06' AS [Date] UNION ALL
SELECT 7 AS [ID] ,N'2013-08-07' AS [Date] UNION ALL
SELECT 8 AS [ID] ,N'2013-08-08' AS [Date] UNION ALL
SELECT 9 AS [ID] ,N'2013-08-09' AS [Date] UNION ALL
SELECT 10 AS [ID] ,N'2013-08-10' AS [Date] UNION ALL
SELECT 11 AS [ID] ,N'2013-08-11' AS [Date] UNION ALL
SELECT 12 AS [ID] ,N'2013-08-12' AS [Date] UNION ALL
SELECT 13 AS [ID] ,N'2013-08-13' AS [Date] UNION ALL
SELECT 14 AS [ID] ,N'2013-08-14' AS [Date] UNION ALL
SELECT 15 AS [ID] ,N'2013-08-15' AS [Date] UNION ALL
SELECT 16 AS [ID] ,N'2013-08-16' AS [Date] UNION ALL
SELECT 17 AS [ID] ,N'2013-08-17' AS [Date] UNION ALL
SELECT 18 AS [ID] ,N'2013-08-18' AS [Date] UNION ALL
SELECT 19 AS [ID] ,N'2013-08-19' AS [Date] UNION ALL
SELECT 20 AS [ID] ,N'2013-08-20' AS [Date] UNION ALL
SELECT 21 AS [ID] ,N'2013-08-21' AS [Date] UNION ALL
SELECT 22 AS [ID] ,N'2013-08-22' AS [Date] UNION ALL
SELECT 23 AS [ID] ,N'2013-08-23' AS [Date] UNION ALL
SELECT 24 AS [ID] ,N'2013-08-24' AS [Date] UNION ALL
SELECT 25 AS [ID] ,N'2013-08-25' AS [Date] UNION ALL
SELECT 26 AS [ID] ,N'2013-08-26' AS [Date] UNION ALL
SELECT 27 AS [ID] ,N'2013-08-27' AS [Date] UNION ALL
SELECT 28 AS [ID] ,N'2013-08-28' AS [Date] UNION ALL
SELECT 29 AS [ID] ,N'2013-08-29' AS [Date] UNION ALL
SELECT 30 AS [ID] ,N'2013-08-30' AS [Date] UNION ALL
SELECT 31 AS [ID] ,N'2013-08-31' AS [Date]

Solution :
In this solution, I used IIF & Format function introduced in SQL Server 2012.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE [tempdb]
GO
SELECT [ID],[Date],FORMAT([Date],'d'
+IIF(DAY([Date]) IN (1,21,31),'''st'''
,IIF(DAY([Date]) IN (2,22),'''nd'''
,IIF(DAY([Date]) IN (3,23),'''rd''','''th''')))
+' MMMM yyyy') As [Formatted Date]
FROM tbl_sample
--OUTPUT

date format to st_rd_th.1.1

Let me know if you come across such issues and the proposed solution.

Read Full Post »

SQL SERVER – Convert a datetime to a short date format

Posted in Solutions, SQL SERVER, tagged , , , , , on June 16, 2013| 2 Comments »

Datetime conversion from one format to another format is sometimes very critical. You should be very careful while doing it, since even a single digit can mess up everything. Recently, I was working on a Report and I needed to convert datetime to a short date format in SQL Server. Given below are three different methods we can use to achieve it.

Method 1 :
In this method, we will convert the datetime value to short date format, using CONVERT function. Here goes the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT @datetime AS [DATE TIME],
CONVERT(VARCHAR(11), @datetime, 100) AS [SHORT DATE TIME]
GO
--OUTPUT

date2shortdate

Method 2 :
In this method, we will convert the datetime to short date format using CAST function. Script given below.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT @datetime AS [DATE TIME],
CAST(@datetime AS VARCHAR(11)) AS [SHORT DATE TIME]
GO
--OUTPUT

date2shortdate

Method 3 :
In this method, we will convert the datetime to short date format using FORMAT function. Script as follows:

--This script is compatible with SQL Server 2012 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT @datetime AS [DATE TIME],
FORMAT(@datetime, 'MMM dd yyyy') AS [SHORT DATE TIME]
GO
--OUTPUT

date2shortdate

Conclusion :
As you can see, we used three different methods (CONVERT, CAST & FORMAT functions)  to convert datetime to short date format and all of them gave the same result set. Nonetheless, I usually use Method 1 (CONVERT function) because it is compatible with different versions of SQL SERVER and also the performance is better than other two.

What do you prefer? Do write to me.

Read Full Post »

SQL SERVER – Multiple ways to remove Milliseconds from Datetime (Truncate Datetime till Second)

Posted in Solutions, SQL SERVER, tagged , , , , on May 30, 2013| 4 Comments »

I came across this problem recently, when I was working on a report and I needed to truncate the date time till seconds. Given below are the two different methods we can use to achieve it.

Method 1 :
In this method, we will first convert the datetime value to varchar, truncate it and then convert it back to datetime data type using CONVERT function twice.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT
@datetime AS [Datetime with millisconds]
,CONVERT(DATETIME,CONVERT(VARCHAR(20),@datetime,120))
AS [Datetime without millisconds]
GO
--OUTPUT

truncatemilliseconds

Method 2 :
In this method, we will first FORMAT the datetime value to a defined format (truncate milliseconds) and then convert it back to datetime data type using CAST function.

--This script is compatible with SQL Server 2012 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT
@datetime AS [Datetime with millisconds]
,CAST(FORMAT(@datetime,'yyyy-MM-dd HH:mm:ss') AS datetime)
AS [Datetime without millisconds]
GO
--OUTPUT

truncatemilliseconds1.1

Read Full Post »

SQL SERVER – How to get short month name / weekday name from datetime

Posted in Solutions, SQL SERVER, tagged , , , , , on May 23, 2013| Leave a Comment »

I came across this question on multiple forums and it is being solved using multiple function such as DATENAME and LEFT function functions. However, lets solve it using FORMAT function only.
Let me create an example to explain it.

USE tempdb
GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-01')
INSERT INTO tbl_Sample VALUES (2,'2013-02-02')
INSERT INTO tbl_Sample VALUES (3,'2013-03-03')
INSERT INTO tbl_Sample VALUES (4,'2013-04-04')
INSERT INTO tbl_Sample VALUES (5,'2013-05-05')
INSERT INTO tbl_Sample VALUES (6,'2013-06-06')
INSERT INTO tbl_Sample VALUES (7,'2013-07-07')

Method 1 :
In this method, we will use DATENAME & LEFT function to achieve short name of month and weekday.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
[ID]
, [Date]
, LEFT(DATENAME(MONTH,[Date]),3) AS [Short Month Name]
, LEFT(DATENAME(WEEKDAY,[Date]),3) AS [Short Weekday Name]
FROM tbl_Sample
--OUTPUT

MonthAndWeekdayshortname1.1

Method 2 :
In this method, we will use ONLY FORMAT function (shipped in SQL Server 2012) to achieve short name of month and weekday.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT
[ID]
, [Date]
, FORMAT([Date],'MMM') AS [Short Month Name]
, FORMAT([Date],'ddd') AS [Short Weekday Name]
FROM tbl_Sample
--OUTPUT

MonthAndWeekdayshortname1.2

Read Full Post »

SQL SERVER – Get day of week in SQL 2005/2008/2012

Posted in Solutions, SQL SERVER, tagged , , , , , , , on April 18, 2013| Leave a Comment »

How to get the day of the week ? I have been asked this question several times and we do have its solution via DATENAME function. However today we will achieve the same solution using FORMAT function, introduced in SQL Server 2012 as well. Given below are the two methods that help in identifying the day of a week in SQL Server 2005 & SQL Server 2012 respectively.
Method 1 :
Script supported in SQL Server 2005 and above :

--This script will work in SQL Server 2005 and above
Declare @Datetime as datetime
Set @Datetime =getdate()
Select DATENAME(dw , @Datetime) as [Weekday Name]
--OR
Select DATENAME(weekday, @Datetime) as [Weekday Name]
--OUTPUT

Get week day name1.1

Method 2 :
Script supported in SQL Server 2012 and above :

--This script will work in SQL Server 2012 and above
Declare @Datetime as datetime
Set @Datetime =getdate()
Select FORMAT(@Datetime,'dddd') as [Weekday Name]
--OUTPUT

Get week day name1.2

Have you come across any other method ? Do share.

Read Full Post »

SQL Server 2012- Date Formats

Posted in Functions, SQL SERVER, tagged , , , , , , on October 13, 2012| 1 Comment »

Date data type plays an important role in the database and when its subject comes, we cannot skip its formatting as well. Today, we will discuss how to do formatting of date data type without using a single function like Convert, Cast, Substring, DateName, Replace etc.
In SQL Server 2012, we can use one function“FORMAT“ to format date instead of multiple functions.
Let me explain it with simple examples to convert date into different formats using “Format“:

Declare @Date as Date =Getdate()
Select @Date --2012-10-13

Select Format(@Date,'yy.MM.dd') as [yy.MM.dd] --12.10.13

Select Format(@Date,'yyyy.MM.dd') as [yyyy.MM.dd] --2012.10.13

Select Format(@Date,'dd.MM.yyyy') as [dd.MM.yyyy] --13.10.2012

Select Format(@Date,'MM/dd/yy') as [MM/dd/yy] --10/13/12

Select Format(@Date,'MM/dd/yyyy') as [MM/dd/yyyy] --10/13/2012

Select Format(@Date,'dd/MM/yyyy') as [dd/MM/yyyy] --13/10/2012

Select Format(@Date,'dd-MM-yyyy') as [dd-MM-yyyy] --13-10-2012

Select Format(@Date,'dd MMM yyyy') as [dd MMM yyyy] --13 Oct 2012

Select Format(@Date,'MMM dd, yyyy') as [MMM dd, yyyy] --Oct 13, 2012

Select Format(@Date,'MM-dd-yy') as [MM-dd-yy] --10-13-12

Select Format(@Date,'MM-dd-yyyy') as [MM-dd-yyyy] --10-13-2012

Select Format(@Date,'yy/MM/dd') as [yy/MM/dd] --12/10/13

Select Format(@Date,'yyyy/MM/dd') as [yyyy/MM/dd] --2012/10/13

Select Format(@Date,'yyMMdd') as [yyMMdd] --121013

Select Format(@Date,'yyyyMMdd') as [yyyyMMdd] --20121013

Select Format(@Date,'yy-MM-dd') as [yy-MM-dd] --12-10-13

Select Format(@Date,'yyyy-MM-dd') as [yyyy-MM-dd] --2012-10-13

Select Format(@Date,'MM/yy') as [MM/yy] --10/12

Select Format(@Date,'MM/yyyy') as [MM/yyyy] --10/2012

Select Format(@Date,'yy/MM') as [yy/MM] --12/10

Select Format(@Date,'yyyy/MM') as [yyyy/MM] --2012/10

Select Format(@Date,'MMMM dd,yyyy') as [MMMM dd,yyyy] --October 13,2012

Select Format(@Date,'MMM yyyy') as [MMM yyyy] --Oct 2012

Select Format(@Date,'MMMM yyyy') as [MMMM yyyy] --October 2012

Select Format(@Date,'dd MMMM') as [dd MMMM] --13 October

Select Format(@Date,'MMMM dd') as [MMMM dd] --October 13

Select Format(@Date,'dd MMMM yy') as [dd MMMM yy] --13 October 12

Select Format(@Date,'dd MMMM yyyy') as [dd MMMM yyyy] --13 October 2012

Select Format(@Date,'MM-yy') as [MM-yy] --10-12

Select Format(@Date,'MM-yyyy') as [MM-yyyy] --10-2012

Select Format(@Date,'yy-MM') as [yy-MM] --12-10

Select Format(@Date,'yyyy-MM') as [yyyy-MM] --2012-10

Select Format(@Date,'MMddyy') as [MMddyy] --131012

Select Format(@Date,'MMddyyyy') as [MMddyyyy] --10132012

Select Format(@Date,'ddMMyy') as [ddMMyy] --131012

Select Format(@Date,'ddMMyyyy') as [ddMMyyyy] --13102012

Select Format(@Date,'MMM-yy') as [MMM-yy] --Oct-12

Select Format(@Date,'MMM-yyyy') as [MMM-yyyy] --Oct-2012

Select Format(@Date,'dd-MMM-yy') as [dd-MMM-yy] --13-Oct-12

Select Format(@Date,'dd-MMM-yyyy') as [dd-MMM-yyyy] --13-Oct-2012

Read Full Post »