. NET Core 1.1 Date Resolution No API, SQL Server data conversion JSON

Objective

In the bulk import of data in Excel found that the date of birth is an integer that is the number of days, the result Daoteng, this is one, and the other is a database in a column is JSON data, but the scene is to run as a job, at this time have to filter out the data manually into JSON, A little way to the children's shoes.

Date issues

The date of birth we gave in Excel is the correct date format, such as the following:

However, when we import data and find that its date is converted to days, it's not really a problem, but the project is in. NET core, and there is a problem in the. NET Framework, if we get to the date of birth is a double number that is the number of days, How do we convert it to the corresponding date, see the following:

var birthDate = datetime.fromoadate (33243);

Now that we can parse the date through the datetime.fromoadate method, let's take a look at the current situation in. NET Core version 1.1:

At this point you will find that this method is not available in. NET Core 1.1.

Now that we've added days to the date when Excel was imported, we're not going to be able to do that, but in net Core 1.1, you can only convert manually. Let's look at the following:

var birthDate = DateTime.Now.AddDays (-33243);

The date obtained is as follows:

The dates we get through the. NET framework above are 1991---- -- ------------------ 1992 - the conversion failed. The above we are by the current date to subtract the number of days resulting in conversion failure, at this time we change the idea, from a date to add this date is not possible. The minimum date is from 1899- - ----and we'll proceed to the next step:

var New DateTime (1899). AddDays (33509);

The results are as follows:

The result of the above demo is 1991-- ----------and our date in Excel is 1991--- After reviewing the data found that this is a bug in Excel, at this point we need to do the following.

            var 33509 ;             if (days>) days                -=1;             var New DateTime (1899). AddDays (days);

The date in Excel adds a day, similar to the following:

Think of this, from a positive perspective, in Excel we define the date type, and why the conversion to the number of days, we look at the formatting in Excel.

This is the case, the default is general, then we can set the custom date format.

Our custom format for the date format will not have to read the conversion, it is not cool, we look at the results:

Whether it is from the root and the use of code to transform both can solve the problem, the above we from the Excel problem estimate also insurance a little.

Converting SQL Server data to JSON

Using code to convert to JSON is simply so easy. But the above also shows the scenario, filtering data directly through the stored procedure and storing the data as JSON strings for SMS notifications, let's look at creating a test table.

CREATE TABLE productlist (Id INT IDENTITY (1,1) PRIMARY KEY, ProductName VARCHAR (  - ), Price NUMERIC (2), Quantity INT, Inserton DATETIME)

Insert test data:

INSERT  into ProductList        (        ProductName, price        ,        quantity,        Inserton        ) VALUES ('  Digital camera',20000,5, GETDATE ()), ('Laptop' ,10000,5, GETDATE ())

Next we will convert the its first to XML, through the FOR XML PATH, as follows:

SELECT * from productlist for XML PATH ('jeffcky')

I don't need to talk about that anymore. The FOR XML Path parameter is defined as the XML root node name

Expands XML, it is the following XML data

The support for JSON parsing in SQL Server 2016 is good, and in SQL Server 2012 it is not possible to use the stuff function for stitching.

DECLARE @content VARCHAR (MAX) SET @content= (SELECT'['+STUFF (SELECT TOP2 ',{"ProductName":"' + ProductName + '"," Price":"' + CONVERT (VARCHAR, price) + '","Quantity":"' + CONVERT (VARCHAR, quantity) + '","Inserton":"' + CONVERT (VARCHAR, Inserton, +) + '"}'From productlist for XML PATH ("')),1,1,"' )  +']'[Productdetail]) PRINT @content

At this point we find that & #x0D happens to be a newline, and we put it on one line of data.

= (select ' [' + STUFF (select TOP 2 ', {"ProductName": "' + ProductName + '", "Price": "' + CONVERT (VARCHAR, price) + '",  "Quantity": "' + CONVERT (varchar, Quantity) + '", "Inserton": "' + CONVERT (varchar, Inserton, +) + '"} ' from  ProductListfor  XML PATH ('11, '  )  ' ]'[productdetail]) PRINT @content

At this point the output will be normal:

Summarize

There is no API for converting days to dates in. NET Core 1.1, and expect 2.0 to have more APIs for us to use.

. NET Core 1.1 Date Resolution No API, SQL Server data conversion JSON