Generating json from bad dates in mysql format in asp

If you want to create 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 1

      

        
        
        
      

    

Returns

Note. Closed is optional. Remove "LEFT" Join Final Request

Now, if you want to create a JSON String in SQL Server and you are not in 2016, we can customize the final query and add the 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"
}]

      

        
        
        
      

    

UDF if Interest

CREATE FUNCTION [dbo].[udf-Str-JSON] (@IncludeHead int,@ToLowerCase int,@XML xml)
Returns varchar(max)
AS
Begin
    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
*/