Dropping multiple tables with one DROP statement

Do you know that you can drop multiple tables using a sing DROP statement?

Assume that in your code or procedure you use many temporary tables and at the end you want to drop all of them. One method is to drop them one by one

Assume the following temporary tables

create table #t1(id int);
create table #t2(id int);
create table #t3(id int);

Usual method of dropping them is

drop table #t1
drop table #t1
drop table #t1

However, simply you can use single DROP statement with each table names seperated by comma

drop table #t1,#t2,#t3

How many of you know this?

Question of the month January 2017 – How does ORDER BY(select statement) work?

Consider the following statement

select 'SQL Server' as db
order by (select 'a')

The result is

db
———-
SQL Server

Now the question is “What does select ‘a’ mean and how does it work in ORDER BY clause?”

Shortest method to extract decimal value

One of the questions asked in a forum.  “What are the different methods to extract decimal part from a number?”.

There can be many methods. Two simple methods are as shown below

1 CAST to INT and subtract from original number

declare @amount decimal(12,2)
set @amount=1200.75
select @amount-cast(@amount as int) as decimal_value

2 Use Modulus operator

declare @amount decimal(12,2)
set @amount=1200.75
select @amount % 1 as decimal_value

The result is

decimal_value
---------------------------------------
0.75

Using modulus operator is the shortest method of all methods that you can use

Finding If Date is LastDay of the Month – Part 2

In my earlier post I showed two methods on how to know if the given date is the last day of month. There are two more methods to know the same which I provide in this post

Consider the following data set

create table #temp(dates datetime)

insert into #temp
select '20090114' union all
select '20080131 10:10:10' union all
select '20070111' union all
select '20090330' union all
select '20120229' union all
select '20100228'

Method 1 : Use EOMONTH function (works from version 2012 onwards)

select 
	dates,
	case 
		when cast(dates as date)=eomonth(dates) then 
			'yes' 
		else 
			'no' 
	end 
		as is_lastday
from 
	#temp

Method 2 : Subtract day part from original date and add one month

select 
	dates,
	case 
		when dates=dateadd(month,1,dates-day(dates)) then 
			'yes' 
		else 'no' 
	end 
		as is_lastday
from 
	#temp

The result of the above two queries are

dates                   is_lastday
----------------------- ----------
2009-01-14 00:00:00.000 no
2008-01-31 10:10:10.000 yes
2007-01-11 00:00:00.000 no
2009-03-30 00:00:00.000 no
2012-02-29 00:00:00.000 yes
2010-02-28 00:00:00.000 yes