You cannot use Dynamic SQL in an User Defined Function. Why is it not possible?
Find out Last day of a month
In SQL Server 2012, there is a function called EOMONTH which returns the last day of a month
Let us consider the following example
DECLARE @DATE DATETIME
SET @DATE='20040211'
SELECT EOMONTH(@DATE)
The result is
last_day
----------
2004-02-29
In earlier versions you can use the following methods to find out it
Method 1 : Find first day of next month and subtract 1 day
DECLARE @DATE DATETIME
SET @DATE='20040211'
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@DATE)+1,-1) AS LAST_DAY
Method 2 : Subtract day part from date and add 1 month
DECLARE @DATE DATETIME
SET @DATE='20040211'
SELECT DATEADD(MONTH,1,DATEADD(DAY,-DAY(@DATE),@DATE)) AS LAST_DAY
Random Password Generator
There can be several ways to generate a random password. Assume that your password should contain alphabets, numbers and some special characters. The following is one of the ways.
declare @password varchar(20)
set @password=''
SELECT
(
SELECT ”+password FROM
(
select top 8 char(number) as password from master..spt_values
where type=‘p’ and number between 48 and 122
order by newid()
) as t
FOR XML PATH(”)
) as password
The above creates some random password with 8 characters in length. If you want to change the length, change the number in the TOP clause.
The password I got running this code is T5:DZ?ow
