Hi Guys
I have got two columns- one is the year and the other is the month number. I need to combine these two columns so that they form a date
For Ex
Year Month CombinedColumn
2000 11 2000/11
2003 01 2003/01
I am using SQL Server 2005
Thanks
I'll assume that Year and Month are integers and CombinedColumn is of data type DATETIME. If other data types are used, this solution might not apply.
Code Block
CREATE TABLE #Temp
(
[Year] INT NOT NULL,
[Month] INT NOT NULL,
[CombinedCol] DATETIME NULL
)
INSERT INTO #Temp VALUES (2000, 11, NULL)
INSERT INTO #Temp VALUES (2003, 01, NULL)
UPDATE #Temp
SET [CombinedCol] = DATEADD(month, [Month] - 1, DATEADD(year, [Year] - 1900, '1900-01-01 00:00:00.000'))
SELECT * FROM #Temp
|||you can try this..
select cast( (cast(yearValue as varchar(4))+cast(monthValue as varchar(2)) + '01') as datetime)
you will get the date as the first day of your year and month values...
|||Harish has the right idea, but it should be noted that 2000/11 is not a valid date. You can just concatenate them for display, but as for making them a date, can you explain more what you will do with the dates once you have them concatenated? (unless just making them the first day of the month suffices, of course 
My query would be like select cast('20001101' as datetime) which sql server implicitly converts to datetime value from varchar if it is in yyyymmdd format. The user needs to decide which value he needs for the dd value in the string
|||Here it is,
Code Block
Create Table #sampledata (
[Year] varchar(4),
[Month] varchar(2)
);
Insert Into #sampledata Values('2000','11');
Insert Into #sampledata Values('2003','01');
DECLARE @.UserDay varchar(2);
Set @.UserDay = '5'
Select Convert(Datetime, Year + Month + substring(cast((cast(@.UserDay as int) + 100) as varchar),2,2),112) [Output] from #sampledata
|||Another method with fewer keystrokes
Code Block
SELECT CAST(LTRIM([Year] * 10000 + [Month] * 100 + @.UserDay) AS DATETIME)
FROM #sampledata
|||Is it bad to use implicit conversion? I believe it will be faster than explicitly handling the conversion.|||Well, I think it is better to use explicit conversion. That way you leave no room for anyone for interpretation and avoid any ambiguities.
Just a note: "Partial Dates" like "January, 1968" are not really supported. Basically in SQL, if you really want to work with dates, you must store a full date...."January 1, 1968" for example. SQL will infer the time component as being Midnight and store it as "01 Jan 1968 00:00" (if you're using smalldatetime) and "01 Jan 1968 00:00:00.000" using DateTime.
So, for your query, let's add a day-of-the-month component:
Code Block
select convert(smalldatetime,'01' + '/' + Month + '/' + Year) as ThisIsTheDate fromor...if the values are stored as numbers (which is not suggested in your example of "01" which would probably be "1" if it were stored as a number:
Code Block
select convert(smalldatetime,"01/" + right('00',convert(varchar,month),2) + '/' + convert(varchar,year))
from
|||Thanks .It works fine
Cheers
|||Actually, here's one that I like better:
Note that DateAdd(year,50,0) = 'January 1, 1950' and DateAdd(month, 5, 0) = 'June 1, 1900' so....
Code Block
CREATE TABLE #Temp
(
[Year] INT NOT NULL,
[Month] INT NOT NULL,
[CombinedCol] DATETIME NULL
)
INSERT INTO #Temp VALUES (2000, 11, NULL)
INSERT INTO #Temp VALUES (2003, 01, NULL)
select dateadd(month, [Month]-1,dateadd(Year,[year]-1900,0))
from #Temp
|||I came up with the same approach, but after you. One little thing...rather than specify the "base date" with a string of "1900-01-01 00:00:00.000") you can simply use the numeric 0
Code Block
select DateAdd(month, 10, 0)
select DateAdd(month, 10, "1900-01-01 00:00:00.000")
