Create a date from day month and year with T-SQL

For SQL Server versions below 12 i can recommend use of `CAST` in combination with `SET DATEFORMAT`

-- 26 February 2015
SET DATEFORMAT dmy
SELECT CAST('26-2-2015' AS DATE)

SET DATEFORMAT ymd
SELECT CAST('2015-2-26' AS DATE)

how you create those strings is up to you

Try this query:


SELECT SUBSTRING(CONVERT(VARCHAR,JOINGDATE,103),7,4)AS
YEAR,SUBSTRING(CONVERT(VARCHAR,JOINGDATE,100),1,2)AS
MONTH,SUBSTRING(CONVERT(VARCHAR,JOINGDATE,100),4,3)AS DATE FROM EMPLOYEE1


Result:

2014 Ja 1
2015 Ja 1
2014 Ja 1
2015 Ja 1
2012 Ja 1
2010 Ja 1
2015 Ja 1

I personally Prefer Substring as it provide cleansing options and ability to split the string as needed. The assumption is that the data is of the format 'dd, mm, yyyy'.

--2012 and above
SELECT CONCAT (
RIGHT(REPLACE(@date, ' ', ''), 4)
,'-'
,RIGHT(CONCAT('00',SUBSTRING(REPLACE(@date, ' ', ''), CHARINDEX(',', REPLACE(@date, ' ', '')) + 1, LEN(REPLACE(@date, ' ', '')) - CHARINDEX(',', REPLACE(@date, ' ', '')) - 5)),2)
,'-'
,RIGHT(CONCAT('00',SUBSTRING(REPLACE(@date, ' ', ''), 1, CHARINDEX(',', REPLACE(@date, ' ', '')) - 1)),2)
)

--2008 and below
SELECT RIGHT(REPLACE(@date, ' ', ''), 4)
+'-'
+RIGHT('00'+SUBSTRING(REPLACE(@date, ' ', ''), CHARINDEX(',', REPLACE(@date, ' ', '')) + 1, LEN(REPLACE(@date, ' ', '')) - CHARINDEX(',', REPLACE(@date, ' ', '')) - 5),2)
+'-'
+RIGHT('00'+SUBSTRING(REPLACE(@date, ' ', ''), 1, CHARINDEX(',', REPLACE(@date, ' ', '')) - 1),2)

Here is a demonstration of how it can be sued if the data is stored in a column. Needless to say, its ideal to check the result-set before applying to the column

DECLARE @Table TABLE (ID INT IDENTITY(1000,1), DateString VARCHAR(50), DateColumn DATE)

INSERT INTO @Table
SELECT'12, 1, 2007',NULL
UNION
SELECT'15,3, 2007',NULL
UNION
SELECT'18, 11 , 2007',NULL
UNION
SELECT'22 , 11, 2007',NULL
UNION
SELECT'30, 12, 2007 ',NULL

UPDATE @Table
SET DateColumn = CONCAT (
RIGHT(REPLACE(DateString, ' ', ''), 4)
,'-'
,RIGHT(CONCAT('00',SUBSTRING(REPLACE(DateString, ' ', ''), CHARINDEX(',', REPLACE(DateString, ' ', '')) + 1, LEN(REPLACE(DateString, ' ', '')) - CHARINDEX(',', REPLACE(DateString, ' ', '')) - 5)),2)
,'-'
,RIGHT(CONCAT('00',SUBSTRING(REPLACE(DateString, ' ', ''), 1, CHARINDEX(',', REPLACE(DateString, ' ', '')) - 1)),2)
)

SELECT ID,DateString,DateColumn
FROM @Table

You can also use

select DATEFROMPARTS(year, month, day) as ColDate, Col2, Col3
From MyTable Where DATEFROMPARTS(year, month, day) Between @DateIni and @DateEnd

Works in SQL since ver.2012 and AzureSQL

Try this:

Declare @DayOfMonth TinyInt Set @DayOfMonth = 13
Declare @Month TinyInt Set @Month = 6
Declare @Year Integer Set @Year = 2006
-- ------------------------------------
Select DateAdd(day, @DayOfMonth - 1,
DateAdd(month, @Month - 1,
DateAdd(Year, @Year-1900, 0)))

It works as well, has added benefit of not doing any string conversions, so it's pure arithmetic processing (very fast) and it's not dependent on any date format
This capitalizes on the fact that SQL Server's internal representation for datetime and smalldatetime values is a two part value the first part of which is an integer representing the number of days since 1 Jan 1900, and the second part is a decimal fraction representing the fractional portion of one day (for the time) --- So the integer value 0 (zero) always translates directly into Midnight morning of 1 Jan 1900...


or, thanks to suggestion from @brinary,

Select DateAdd(yy, @Year-1900,
DateAdd(m, @Month - 1, @DayOfMonth - 1))

Edited October 2014. As Noted by @cade Roux, SQL 2012 now has a built-in function:
`DATEFROMPARTS(year, month, day)`
that does the same thing.

Edited 3 Oct 2016, (Thanks to @bambams for noticing this, and @brinary for fixing it), The last solution, proposed by @brinary. does not appear to work for leap years unless years addition is performed first

select dateadd(month, @Month - 1,
dateadd(year, @Year-1900, @DayOfMonth - 1));

I know the OP is asking for SQL 2005 answer but the question is pretty old so if you're running SQL 2012 or above you can use the following:

SELECT DATEADD(DAY, 1, EOMONTH(@somedate, -1))

Reference:

[To see links please register here]