Want to Concatenate Year and Month to get a Date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Want to Concatenate Year and Month to get a Date

Author

Topic

Joozh
Posting Yak Master

145 Posts

Posted - 2004-05-14 : 03:15:09

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

Thanks & Regards.

-J

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-14 : 03:35:54

Ill assume your Month and year columns are numeric

Convert(datetime,
(convert(varchar(4), year) + '/' + right('0' + convert(varchar(2), month)) + '/01')
, 111)

I have put the string handling on separate lines so it is a little clearer, hope this helps

With regards to the formatting issue, I have looked at the reply option at the top of the page, rather than the quick reply at the bottom - I think all the functionality is here

Graham

Joozh
Posting Yak Master

145 Posts

Posted - 2004-05-14 : 06:10:33

Hi,

Many thanks... I created a view based on my table, selected the Year and MOnth fields (both of which are smallint) and put in your suggestion... BUT when I tried to execute the view I get an error saying:
"..... The right function requires 2 arguments".

Any clues?

Thanks & Regards.

-J

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-05-14 : 07:35:21

select dateadd(mm,month,dateadd(yy,(year-1900),'19000101') from yourtable

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-14 : 07:57:52

Check out the sqlteam weblogs under "JeffS"; I have posted a couple of methods (both a formula and a UDF) to do this.

- Jeff

Joozh
Posting Yak Master

145 Posts

Posted - 2004-05-14 : 08:23:25

Many thanks for the info. about Weblogs. Will check them out.

Thanks & Regards.

-J

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-05-14 : 08:57:50

quote:
Originally posted by Joozh
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

Insert Code button looks like - "#" Build your formatted post in a text editor like notepad or QA and copy and paste it between the html tags "[ code] [ /code]"

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-16 : 12:19:28

Sorry, Ive been away
In the interest of completeness, my suggestion should have read
Ill assume your Month and year columns are numeric

Convert(datetime,
(convert(varchar(4), year) + '/' + right('0' + convert(varchar(2), month), 2) + '/01')
, 111)

I missed off the ', 2' from the right function.

The Enigma - I liked your suggestion

Graham

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-16 : 12:37:29

A bit simpler and can leave off the style is

Convert(datetime,
(convert(varchar(4), year) + right('0' + convert(varchar(2), month), 2) + '01')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-16 : 14:52:32

w/o converts or worrying about styles or date formats (where @y=year, @m=month, @d=day) :

dateadd(yy,(@y-1900),0) + dateadd(mm,@m-1,0) + @d-1

another is:

dateadd(mm,(@y-1900) * 12 + @m-1 ,0 ) + @d-1

- Jeff

Joozh
Posting Yak Master

145 Posts

Posted - 2004-05-17 : 06:22:44

Thanks all for your replies. WIll give them a try.

Request for Dr. Cross Join:

quote:

Check out the sqlteam weblogs under "JeffS"; I have posted a couple of methods (both a formula and a UDF) to do this.

I must be searcing in the wrong place.... Can you kindly help me find the methods/formulas/UDF's under the weblogs.

Thanks & Regards.

-J

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-17 : 08:03:00

here's an example of a UDF that might work well for you:

http://weblogs.sqlteam.com/jeffs/archive/2003/12/09/646.aspx

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources