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