Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories




Create a Date in T-SQL

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.


Print | posted on Monday, December 15, 2003 10:07 AM | Filed Under [ T-SQL Code Library - SQL DateTime Data ]



# 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.
5/3/2006 1:03 PM | matt johnson

# 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)
5/3/2006 3:00 PM | Jeff

# 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.
3/18/2009 9:41 AM | Original Sin

# re: Create a Date in T-SQL

select convert(datetime,@m+'/'+@d+'/'+@y)
10/14/2009 1:55 PM | saurav

# re: Create a Date in T-SQL

convert(datetime, Convert(varchar,@m) + '-' + Convert(varchar,@d) + '-' + Convert(varchar,@y) )
1/21/2010 2:44 AM | marco

# 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
7/12/2010 11:23 AM | javed
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET