en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


Read first, before you you use the blog

Maximize
מרץ20

Written by: ronen ariely
20/03/2018 14:57 RssIcon

Background

Several years back I wrote a post about Date displaying format, vs Date storing format, and today I want to focus on using the parameter style in CONVERT function. It is directly relevant to my previous post, and I highly recommend to read it first.

* This post based on this question at the MSDN forum.

Discussion throw demo

I think that in this case the best approach to explain the issue, will be based on demo. I will discuss and explain the behavior during the demo according to the results we get.

Our goal: display data in format dd/mm/yyyy

Will the bellow query give us the expected result?

declare @ExpiryDate nvarchar(10) = '2018-02-02'
Select CONVERT(date,@ExpiryDate,103)
GO

The answer is no. In fact this example will raise an error...

While converting the string to date using the T-SQl CONVERT function, SQL Server uses the style parameter (103 in this case) in order to "understand" what is the format of the date in the string. In our case the format of the string fits style 120 which is yyyy-mm-dd but style number 103 is dd/mm/yyyy and since the input does not fit this format, the execution fails with an error:

Conversion failed when converting date and/or time from character string.

You should remember that the input format should fit the style of the convert!

* '2018-02-02' is the displayed format, once we insert this as a value to the database, the information stored in our database according to the data type, which in our case is string type NVARCHAR, and the data is stored in the disk according to the stored format as you can read in my previous post. By using the function CONVERT we use the data from the table as input for the function and SQL Server must know what is the input format. the input format is in this case is the same as the displayed format.

Bellow query should work fine, since the style format in the CONVERT fits the input format

declare @ExpiryDate nvarchar(10) = '2018-02-02'
Select CONVERT(date,@ExpiryDate,120)
GO

But what is the output that SQL Server actually send, what is the output which we get from the server, and what is the output displayed that we see in the client app?!?

SQL Server returns the data as stream of bytes according to the type of the data and we get it according to the provider which we are using to connect the server. The output in our case is type DATE and it is pass from the server to the client according to the provider which we used to connect to the server. Dot.NET supports binary serialization types like System.Data.SqlTypes.SqlDateTime which fit the SQL Server data type DATETIME, and therefore the data stay in the same format.

* It is best to pass the data as DATE and not to convert it in the server side to string and then pass it to the client as string.

The displayed format of the output for Date depend on the client side. Once the binary data reach the client app, it is displayed by the client application according to the application setting.

Displayed format of date depend on your client app setting if you send the client data type date. Therefore, you can change the displayed format in the client side, for example in C# you can use the Property DataFormatString as demonstrated in this tutorial.

Another option is to change the format in the query level by converting it to a string.

* It usually make no sense to converted the data into string before the server pass it to the client. A DATE only use 3 bytes while a string of 10 characters will need 10 bytes. This is usually not a good idea since string will be larger then Date and you will need to pass more data from the server to the client.

declare @ExpiryDate nvarchar(10) = '2018-02-01'
declare @ExpiryDate2 Date
-- First we convert to Date
Select @ExpiryDate2 = CONVERT(date,@ExpiryDate,120)
-- now we configure the desplayed format while converting the value to String
-- We can use the FORMAT function here:
Select FORMAT(@ExpiryDate2, 'dd/MM/yyyy', 'en-us')
-- Or we can usee CONVERT function again
SELECT CONVERT(VARCHAR, @ExpiryDate2, 103)
-- but remember that from tthis point you do not use Date but String
GO

Conclusion

When we convert data from string to Date, SQL Server uses the style parameter in order to parse the string into date. The style gives the information of the string format and not the format of the returned data. When converting DATE into string the style parameter configures the format of the string that is returned, which configures the displayed format of the value in the application side.

Tags: SQL , T-SQL , CONVERT
Categories: SQL
Location: Blogs Parent Separator Public blog