Archive

Archive for the ‘MS SQL’ Category

Convert sql query result in to JSON

October 30, 2014 Leave a comment

DECLARE 
 @TableName NVARCHAR(512),
 @sql NVARCHAR(MAX),
 @xml XML;


SET @TableName = N'Customers';

SET @sql = N'SELECT @xml = CONVERT(NVARCHAR(MAX), (
 SELECT top 20 * FROM ' + @TableName + ' FOR XML path, root));';

EXEC sp_executesql @sql, N'@xml XML OUTPUT', @xml OUTPUT;

SELECT dbo.ToJSON (@xml)


USE [DB]
GO
/****** Object: UserDefinedFunction [dbo].[ToJSON] Script Date: 10/30/2014 19:24:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ToJSON] (@XMLResult XML)
RETURNS NVARCHAR(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @JSONVersion NVARCHAR(MAX), @Rowcount INT
SELECT @JSONVersion = '', @rowcount=COUNT(*) FROM @XMLResult.nodes('/root/*') x(a)
SELECT @JSONVersion=@JSONVersion+
STUFF(
 (SELECT TheLine FROM 
 (SELECT ',
 {'+
 STUFF((SELECT ',"'+COALESCE(b.c.value('local-name(.)', 'NVARCHAR(255)'),'')+'":"'+
 REPLACE( --escape tab properly within a value
 REPLACE( --escape return properly
 REPLACE( --linefeed must be escaped
 REPLACE( --backslash too
 REPLACE(COALESCE(b.c.value('text()[1]','NVARCHAR(MAX)'),''),--forwardslash
 '\', '\\'), 
 '/', '\/'), 
 CHAR(10),'\n'), 
 CHAR(13),'\r'), 
 CHAR(09),'\t') 
 +'"' 
 FROM x.a.nodes('*') b(c) 
 FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')+'}'
 FROM @XMLResult.nodes('/root/*') x(a)
 ) JSON(theLine)
 FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)' )
,1,1,'')
IF @Rowcount>1 RETURN '['+@JSONVersion+'
]'
RETURN @JSONVersion
END

Categories: MS SQL Tags: , , ,

Format SQL Date hh:mm AM/PM

March 11, 2013 1 comment

SELECT RIGHT( convert(varchar(25),GETDATE(),101)  +’ ‘+ CASE WHEN DATEPART(HH,GETDATE()) < 13 THEN RIGHT(REPLICATE(‘0’,2) + CAST(datepart( HH,GETDATE()) AS VARCHAR(2)),2) ELSE RIGHT(REPLICATE(‘0′,2) + CAST(datepart( HH, dateadd(HH, -12 ,GETDATE())) AS VARCHAR(2)),2) END +’:’+ RIGHT(REPLICATE(‘0′,2) + CAST(datepart( mi,GETDATE()) AS VARCHAR(2)),2) +’ ‘+ CASE WHEN DATEPART(HH,GETDATE()) < 13 THEN ‘AM’ ELSE ‘PM’ END,8)

Got a big sql statement to show the time format (hh:mm AM/PM) from date.

If any simple query to do it, please let me know.

🙂

Categories: MS SQL

Convert DateTime to Date MS SQL

February 15, 2013 Leave a comment

select convert(varchar, getdate(), 105)

This convert datetime to date using sql query.

For detail refer

http://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

http://www.sql-server-helper.com/tips/date-formats.aspx

Categories: MS SQL Tags:

Case Sensitive WHERE Condition MS SQL Server

February 13, 2013 1 comment

I can see the WHERE condition checks the condition case insensitively. Here is the sql query that checks the username case sensitively in SQL Server.

SELECT * FROM [User] WHERE Username = ‘Sree’ Collate SQL_Latin1_General_CP1_CS_AS

For more info refer

http://www.mssqltips.com/sqlservertip/1032/case-sensitive-search-on-a-case-insensitive-sql-server/