Mujstart.cz blog

Alter Function [dbo].[fn_format_date] (@Datetime DateTime, @FormatMask NVarChar(32))
Returns NvarChar(32)
As
Begin
    Declare @StringDate NVarChar(32)
    Set @StringDate = @FormatMask
 
    If (CharIndex (N'YYYY',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'YYYY', DateName(YY, @Datetime))
    End
    If (CharIndex (N'YY',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'YY', Right(DateName(YY, @Datetime),2))
	End  
    If (CharIndex (N'MM',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'MM', Right(N'0'+Convert(VARCHAR,DatePart(MM, @Datetime)),2))
	End
    If (CharIndex (N'M',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'M', Convert(VARCHAR,DatePart(MM, @Datetime)))
	End
    If (CharIndex (N'DD',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'DD', Right(N'0'+DateName(DD, @Datetime),2))
	End
    If (CharIndex (N'D',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'D', DateName(DD, @Datetime))
    End
Return @StringDate
End
 
Go 
Set Nocount On
Declare @lDateTime Datetime = '20110506'
Print [dbo].[fn_format_date] (@lDateTime, 'MM/DD/YYYY')
Print [dbo].[fn_format_date] (@lDateTime, 'DD/MM/YYYY')
Print [dbo].[fn_format_date] (@lDateTime, 'M/DD/YYYY')
Print [dbo].[fn_format_date] (@lDateTime, 'M/D/YYYY') 
Print [dbo].[fn_format_date] (@lDateTime, 'M/D/YY')
Print [dbo].[fn_format_date] (@lDateTime, 'MM/DD/YY')
Print [dbo].[fn_format_date] (@lDateTime, 'YYYY/MM/DD')
Print [dbo].[fn_format_date] (@lDateTime, 'YYYYMMDD')
Print [dbo].[fn_format_date] (@lDateTime, 'YYYY-MM-DD')
 
/*
05/06/2011
06/05/2011
5/06/2011
5/6/2011
5/6/11
05/06/11
2011/05/06
20110506
2011-05-06
*/

SELECT Top 100
	[number]
FROM 
	[master].[dbo].[spt_values] 
ORDER BY 
	[number] Asc
FOR XML PATH('')  
 
SELECT @@ROWCOUNT
--100

Declare @lId UniqueIdentifier = NewId()
Declare @x NVarChar(Max) =
	N'
	Select
		[id]
	From
		[table]
	Where
		[column] = '+IsNull(N'N'''+Cast(@lId As NVarChar(64))+'''', 'Null')
Print @x
/*
	Select
		[id]
	From
		[table]
	Where
		[column] = N'094FDFB4-7665-4E6A-89E0-B6A6C231D617'
*/

Declare @lId UniqueIdentifier
Declare @x NVarChar(Max) =
	N'
	Select
		[id]
	From
		[table]
	Where
		[column] = '+IsNull(N'N'''+Cast(@lId As NVarChar(64))+'''', 'Null')
Print @x
/*
	Select
		[id]
	From
		[table]
	Where
		[column] = Null
*/

DECLARE @s AS VARCHAR(1000) = '.x.x.x.';
 
SELECT REPLACE(@s, '.x.', '.y.');

<com:car
    xsi:schemaLocation="http://yourserver.com/example http://yourserver.com/example.xsd"
    xmlns:com="http://yourserver.com/example">
    <com:name>Text</com:name>
</com:car>