convert to date - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

convert to date

Author

Topic

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-04-10 : 18:04:38

Hi,
I'm having problem trying to convert 'yyyymm' to just 'yyyy' and 'mm'.

this is what my data looks like:

rptmonth:
200706
200707
200708

I would like result to be separated by year and month
rptmonth:
2007 06
2007 07
2007 08

thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 18:08:25

SELECT LEFT(rptMonth, 4) AS rptYear, RIGHT(rptMonth) AS rptMonth
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-04-10 : 18:11:44

Thanks a whole bunch!!!!!!!!!

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-04-11 : 10:33:22

I came across an error!!!!!!!
Here's my code, please tell me where it goes wrong?

select LEFT(rptMonth, 4) AS rptYear, RIGHT(rptMonth) AS rptMonth
,rptmonth,sum(adc)
from dshsdboly7205.prohosp.dbo.tb_state_hosp_adc_monthly
where hospital in ('WSH', 'pals')
group by rptmonth

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-11 : 10:36:25

right(rptMonth,2)

Em

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-04-11 : 10:39:43

Thanks, didn't even see that at all. You're a life saver :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-11 : 10:48:32

or

SELECT Stuff(rptMonth, 5,0,' ') AS rptMonth
FROM Table1

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-11 : 10:59:54

Not quite the same, but worth mentioning...

SELECT rptMonth / 100 AS rptYear, rptMonth % 100 AS rptMonth FROM Table1

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-04-11 : 12:04:41

Thanks those are great.

I also wonder, to split the month to month name, how would you convert it?

for example:

01 Jan
02 Feb
03 Mar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-11 : 12:57:16

LEFT(DATENAME(mm,CAST(yourfield + '01' as datetime)),3)

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-04-11 : 13:29:43

That is so cool. I was doing a case right to get the date. this is an easier way. Thanks a whole bunch!!!!!!!!!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-14 : 03:15:55

quote:
Originally posted by huynhtl

Thanks those are great.

I also wonder, to split the month to month name, how would you convert it?

for example:

01 Jan
02 Feb
03 Mar

You should always use DATETIME datatype to store dates and leve the formation to front end application

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources