Create a Date in T-SQL

Mon Dec 15, 2003 by Jeff Smith in t-sql, code-library-sql, datetime-data

Another way to create dates easily, w/o using CONVERT or using a UDF (i.e., if you have SQL 7.0), if you are starting out with integers representing the year, month and day of the date you need to create:

declare @y int;

declare @m int;

declare @d int;

-- the date we wish to create is Nov 6, 2003:

set @y = 2003

set @m = 11

set @d = 6

select dateadd(yy,(@y-1900),0) + dateadd(mm,@m-1,0) + @d-1

Again, what makes this nice is you are not using CONVERT after concatenating a string together. And it's still pretty short. So if you are starting with a year, month, and day and need to turn it into a date, this is a pretty easy way to do it.

Legacy Comments


matt johnson 2006-05-03	re: Creating Dates in T-SQL This will not work, test it in 200 or 2004 when it is a leap year, throws off the day by 1.

Jeff 2006-05-03	re: Creating Dates in T-SQL Thanks Matt! Don't know how I missed that. It should work logically, but for some reason as you mentioned in a leap-year it is off by 1 day. Here is a formula that works better: select dateadd(mm,(@y-1900)* 12 + @m - 1,0) + (@d-1)

Original Sin 2009-03-18	re: Create a Date in T-SQL Jeff, thanks for your update, it solves the issue of leap years in the original formula. Your code has gone far, I can't go into specifics, but people were put in jail because of it. Thanks again.

saurav 2009-10-14	re: Create a Date in T-SQL select convert(datetime,@m+'/'+@d+'/'+@y)

marco 2010-01-21	re: Create a Date in T-SQL convert(datetime, Convert(varchar,@m) + '-' + Convert(varchar,@d) + '-' + Convert(varchar,@y) )

javed 2010-07-12	re: Create a Date in SQL server in want to enter date in this format 1985-02-06 i create table "member"with attributes create table member ( member_id int primary key ,name char (30) ,date_of_birth datetime) when in insert values insert into member values (1,'jack',1985-03-07) and when i use this command select*from member the result is as 1 jack 19850307 means date of birth values merged plz solve my problem. i m using sql server 2000