generating json from bad mysql-formatted dates in asp
If you want to create the dataset in SQL Server, consider the following
Example
Declare @YourTable table (dayFrom varchar(25),dayTo varchar(25),timeFrom varchar(25),timeTo varchar(25))Insert Into @YourTable values('monday' ,'tuesday','07:00','14:00'),('wednesday','' ,'08:00','12:00'),('thursday' ,'friday' ,'07:30','13:00'),('saturday' ,'' ,'08:00','12:00');with cteD as (Select * From (Values(1,'Monday'),(2,'Tuesday'),(3,'Wednesday'),(4,'Thursday'),(5,'Friday'),(6,'Saturday'),(7,'Sunday')) DDD(DD,DDD) ), cteR as ( Select A.* ,R1 = B.DD ,R2 = IsNull(C.DD,B.DD) From @YourTable A Left Join cteD B on dayFrom = B.DDD Left Join cteD C on dayTo = C.DDD Where 1=1 -- Your WHERE STATEMENT HERE ) Select daySeq = A.DD ,dayOfWeek = A.DDD ,from1 = IsNull(B.TimeFrom,'Closed') ,from2 = IsNull(B.TimeTo,'Closed') From cteD A Left Join cteR B on A.DD between B.R1 and B.R2 Order By 1Returns
Note: The Closed is Optional. Remove the "LEFT" Join in the final query
Now, if you want to create the JSON String in SQL Server, and you're NOT on 2016, we can tweak the final query and add a UDF.
Select JSON=[dbo].[udf-Str-JSON](0,0,( Select daySeq = A.DD ,dayOfWeek = A.DDD ,from1 = IsNull(B.TimeFrom,'Closed') ,from2 = IsNull(B.TimeTo,'Closed') From cteD A Left Join cteR B on A.DD between B.R1 and B.R2 Order By 1 For XML RAW))Returned JSON String
[{ "daySeq": "1", "dayOfWeek": "Monday", "from1": "07:00", "from2": "14:00"}, { "daySeq": "2", "dayOfWeek": "Tuesday", "from1": "07:00", "from2": "14:00"}, { "daySeq": "3", "dayOfWeek": "Wednesday", "from1": "08:00", "from2": "12:00"}, { "daySeq": "4", "dayOfWeek": "Thursday", "from1": "07:30", "from2": "13:00"}, { "daySeq": "5", "dayOfWeek": "Friday", "from1": "07:30", "from2": "13:00"}, { "daySeq": "6", "dayOfWeek": "Saturday", "from1": "08:00", "from2": "12:00"}, { "daySeq": "7", "dayOfWeek": "Sunday", "from1": "Closed", "from2": "Closed"}]The UDF if Interested
CREATE FUNCTION [dbo].[udf-Str-JSON] (@IncludeHead int,@ToLowerCase int,@XML xml)Returns varchar(max)ASBegin Declare @Head varchar(max) = '',@JSON varchar(max) = '' ; with cteEAV as (Select RowNr =Row_Number() over (Order By (Select NULL)) ,Entity = xRow.value('@*[1]','varchar(100)') ,Attribute = xAtt.value('local-name(.)','varchar(100)') ,Value = xAtt.value('.','varchar(max)') From @XML.nodes('/row') As R(xRow) Cross Apply R.xRow.nodes('./@*') As A(xAtt) ) ,cteSum as (Select Records=count(Distinct Entity) ,Head = IIF(@IncludeHead=0,IIF(count(Distinct Entity)<=1,'[getResults]','[[getResults]]'),Concat('{"status":{"successful":"true","timestamp":"',Format(GetUTCDate(),'yyyy-MM-dd hh:mm:ss '),'GMT','","rows":"',count(Distinct Entity),'"},"retults":[[getResults]]}') ) From cteEAV) ,cteBld as (Select * ,NewRow=IIF(Lag(Entity,1) over (Partition By Entity Order By (Select NULL))=Entity,'',',{') ,EndRow=IIF(Lead(Entity,1) over (Partition By Entity Order By (Select NULL))=Entity,',','}') ,JSON=Concat('"',IIF(@ToLowerCase=1,Lower(Attribute),Attribute),'":','"',Value,'"') From cteEAV ) Select @JSON = @JSON+NewRow+JSON+EndRow,@Head = Head From cteBld, cteSum Return Replace(@Head,'[getResults]',Stuff(@JSON,1,1,''))End-- Parameter 1: @IncludeHead 1/0-- Parameter 2: @ToLowerCase 1/0 (converts field name to lowercase-- Parameter 3: (Select * From ... for XML RAW)-- Syntax : Select [dbo].[udf-Str-JSON](0,1,(Select Top 2 RN=Row_Number() over (Order By (Select NULL)),* from [Chinrus-Shared].[dbo].[ZipCodes] Where StateCode in ('RI') for XML RAW))/*Declare @User table (ID int,Active bit,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50))Insert into @User values(1,1,'John','Smith','john.smith@email.com'),(2,0,'Jane','Doe' ,'jane.doe@email.com')Declare @XML xml = (Select * from @User for XML RAW)Select A.ID ,B.JSON From @User A Cross Apply (Select JSON=[dbo].[udf-Str-JSON](0,0,(Select A.* For XML Raw)) ) B*/