# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Want to Concatenate Year and Month to get a Date

## joozh

Hi,

My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)

I'll appreciate your help.

Year	Month		Derived Date
----	------		------------
2004	1		01-Jan-2004
2004	2		01-Feb-2004
2004	3		01-Mar-2004
and so on ....


Many thanks in advance. I'll appreciate your help
*P.S.*
Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them

----------


## MAK

--If month and year are integer
create table dates (Year int, Month int, Deriveddate datetime)

insert into dates(year,month) select 2004, 1 
insert into dates(year,month) select 2004, 2 
insert into dates(year,month) select 2004, 3 
insert into dates(year,month) select 2004, 9 
insert into dates(year,month) select 2004, 12 

select convert(datetime,convert(varchar(4),year)+right('0  '+convert(varchar(2),month),2)+
'01',112) as Deriveddate from dates

----------


## MAK

--If month and year are varchar
create table dates2 (Year varchar(4), Month varchar(2), Deriveddate datetime)

insert into dates2(year,month) select '2004', '1' 
insert into dates2(year,month) select '2004', '2' 
insert into dates2(year,month) select '2004', '3' 
insert into dates2(year,month) select '2004', '9' 
insert into dates2(year,month) select '2004', '12'

Select convert(datetime,Year+right('00'+month,2)+'01',112  ) as deriveddates from dates2

----------


## joozh

Thanks MAK.

In case I forgot to mention, both my Year and MOnth fields are smallint.

When I apply:

CONVERT(datetime, [Year] + RIGHT('00' + [Month], 2) + '01', 112) AS DerivedDate

I get incorrect DerivedDate. For example Year = 1998, Month = 12, The DerivedDate that I'm getting is 05-July-1905 ???

Really sorry to bother you  :Frown:  but any more clues please.

Thanks.

----------


## MAK

--cas' you are using integer year and not converting to varchar.

convert(datetime,convert(varchar(4),year)+right('0  '+convert(varchar(2),month),2)+'01',112) as Deriveddate

----------


## MAK

create table dates (Year smallint, Month smallint, Deriveddate datetime)

insert into dates(year,month) select 2004, 1 
insert into dates(year,month) select 2004, 2 
insert into dates(year,month) select 2004, 3 
insert into dates(year,month) select 2004, 9 
insert into dates(year,month) select 2004, 12 

select convert(datetime,convert(varchar(4),year)+right('0  '+convert(varchar(2),month),2)+'01',112) as Deriveddate from dates

----------

