Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

10 ways to simulate DateSerial function

Jul 19 2012 12:00AM by Madhivanan   

DateSerial function accepts three paramter values year,month and day and return a valid date value with time set to midnight. SQL Server does not support this function until version 2012 is released.

There can be many ways to simulate this functionality. Here are 10 different ways

Consider the following set of data

create table #t(year_col int,month_col int,day_col int)
GO
insert into #t (year_col,month_col,day_col)
select 2001,6,2

Character Handling

Method 1 : Convert the numbers to varchars and into datetime

select 
	convert(datetime,cast(day_col as varchar(2))+'/'+cast(month_col as varchar(2))+'/'+cast(year_col as varchar(4)),103)
from
	#t 

Method 2 : Use STR function and Convert into datetime

select 
	convert(datetime,replace(str(year_col,4)+ str(month_col,2)+str(day_col,2),' ',0),112)
from
	#t 

Method 3 : Use CONCAT function and Convert into datetime(from version 2012 onwards)

select 
	convert(datetime,concat(day_col,'/',month_col,'/',year_col) ,103)
from
	#t 

Method 4 : Make use of implicit varchar convertion and Convert into datetime

select
	convert(datetime,ltrim(day_col)+'/'+ltrim(month_col)+'/'+ltrim(year_col),103)
from
	#t

Date handling using DATEADD functions

Method 5 : Use DATEADD function

select
	dateadd(day,day_col-1,dateadd(month,month_col-1,dateadd(year,year_col-1900,0)))
from
	#t

Method 6 : Use DATEADD function(shortcut to Method 5)

select
	dateadd(month,month_col-1,dateadd(year,year_col-1900,0))+day_col-1
from
	#t

Method 7 : Use DATEADD function (convert year value into month)

select
	dateadd(month,(year_col-1900)*12+month_col-1,0)+day_col-1
from
	#t

Method 8 : Use DATEFROMPARTS function (from version 2012 onwards)

select
	cast(datefromparts(year_col,month_col,day_col) as datetime)
from
	 #t

Method 9 : Use DATETIMEFROMPARTS function (from version 2012 onwards)

select
	datetimefromparts(year_col,month_col,day_col,0,0,0,0) 
from
	 #t

Arithmetic operation

Method 10 : Use Arithmetic operators and convert to datetime

select
	cast(ltrim(year_col*10000+month_col*100+day_col) as datetime)
from
	#t

The result of all above methods is
-----------------------
2001-06-02 00:00:00.000

Important Note : All the above methods are written assuming all the parameter values are VALID (ie Year value has 4 digits and month and day values are greater than zero(either single or double digits - and values like YEAR=634, month=34,day=0 will not work) Also if the any of month or day value is out of range (ie month>12 or day<1 or day>32) the methods shown under DATEADD functions will work while others wont work. DATEADD functions will show future or older dates for these ranges.

Tags: 


Madhivanan
3 · 40% · 12936
10
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

2  Comments  

  • Which is fastest in 2008? 2012?

    commented on Jul 19 2012 11:50AM
    Jeff Moden
    159 · 1% · 305

Your Comment


Sign Up or Login to post a comment.

"10 ways to simulate DateSerial function" rated 5 out of 5 by 10 readers
10 ways to simulate DateSerial function , 5.0 out of 5 based on 10 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]