Convert yyyymm to yyyymmdd - 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 yyyymm to yyyymmdd

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2011-06-21 : 06:33:18

Hi
I have two tables
Fact Sales
CalenderMonth INT
Data
----
200805
200806
200807

Dimesion
DimTime
TimeID INT
Data
-----
20080501
20080601
20080701

Now i want to add 01 in the CalenderMonth data to get the join to fill the TimeId in my fact Table

SELECT dimTime.TimeID FROM FactSales
LEFT JOIN dimTime
ON FactSales.CalenderMonth = DimTime.TimeID

I want the FactSales.CalenderMonth + 01 so that i can join with TimeId

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-21 : 06:47:00

You can add the 01 at the end using this:


CAST(CalendarMonth AS VARCHAR(6))+'01'

And then if you need that to be of datetime data time, cast it again:


CAST(CAST(CalendarMonth AS VARCHAR(6))+'01' as datetime)

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2011-06-21 : 06:50:55

quote:
Originally posted by sunitabeck

You can add the 01 at the end using this:

CAST(CalendarMonth AS VARCHAR(6))+'01'
And then if you need that to be of datetime data time, cast it again:

CAST(CAST(CalendarMonth AS VARCHAR(6))+'01' as datetime)

select CAST( CalendarMonth AS VARCHAR(6))+'01'
FROM FactSales
gives me this error :(

Msg 8115, Level 16, State 5, Line 2
Arithmetic overflow error converting numeric to data type varchar.

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-21 : 07:11:16

What is the result of the following SELECT?
select max(CalenderMonth) from FactSales

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-21 : 07:20:16

This probably means the data is not in the form of YYYYMM as you indicated. For example, try this:


declare @x as bigint
set @X = 200805
print cast(@X as varchar(6))

It would work fine. But if you change 200805 to 20080501, you would get the error that you saw. So you need to investigate what kind of data you have.

You can do select max(CalendarMonth) to see what the maximum numeric value is.

select * from yourTable where isnumeric(CalendarMonth) = 0 will tell you if there are any non-numeric characters in the data.

If it is that there are some rows with just year and month, and some with year, month and a date, that can be parsed, but first you need to know what kind of data irregularities you have.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-06-21 : 10:43:04

select
	CalenderMonth
from
	FactSales
where
	-- Search for invalid YYYYMM in INT column
	1 =
	case
	when CalenderMonth is null
	then 1
	when convert(varchar(20),CalenderMonth) not like '[1-2][0-9][0-9][0-9][0-1][0-9]'
	then 1
	when isdate(convert(varchar(20),CalenderMonth)+'01') <> 1
	then 1
	else 0
	end

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources