Combining the dates

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 from

or...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")