How to get a value from one column to another

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

How to get a value from one column to another

Author

Topic

brentos2004
Starting Member

4 Posts

Posted - 2007-01-04 : 15:32:44

Hi,

Im new to SQL Server 2005 and would appreciate any help with this.

I have a table called dimTime which has 3 columns timeID, timeIn and timeOut. The timeIn field is set to the datetime datatype and contains the exact date and time that a client has entered. What I want to do is get the year and month values from the timeIn field and put them into the timeID field in the format yyyymm. I want it to fill this in for the current 11000 records and then do it automatically for every new record that is added.

Is there a way of doing this? Sorry if im a bit vague im an amateur.

Thanks
Brent

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-01-04 : 16:15:02

Look at DATEPART and DATENAME functions in books online. However, what is it you're trying to accomplish? At first glance, it looks like a bad idea...

Mark

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-04 : 16:17:40

assumes dateId is an int...


update	dimTime
set	timeId =
	Case When DatePart(m, timeIn) < 10 Then
		DatePart(yyyy, timeIn) * 100 + '0' + DatePart(m, timeIn)
	Else
		DatePart(yyyy, timeIn) * 100 + DatePart(m, timeIn)
	End

brentos2004
Starting Member

4 Posts

Posted - 2007-01-04 : 16:29:20

What im trying to create is a time dimension for a prototype datawarehouse solution that im creating for my dissertation. Ive imported the data from an access database into sql server 2005 and am now trying to create simple stars. Ive been told that a dimtime table must be included in every star/cube for each record to be unique. I have copied the timeIn and timeOut data from a table which was already populated in the database and need to create the timeID field using the yyyymm format.

If anyone has any better ideas or tips please let me know.

Thanks
Brent

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-04 : 19:41:10

UPDATE dimTime
SET timeID = CONVERT(VARCHAR(6), timeIN, 112)

Works for both VARCHAR and INT datatype for timeID.
And seems a lot easier to use.

Peter Larsson
Helsingborg, Sweden

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-01-05 : 04:39:21

Have a look at Kimball ([url]www.ralphkimball.com[/url]). It's a good idea to stick to surrogate values (identity columns) for dimension keys in star schemas. One reason is saving space in your fact tables (i.e. each dimension foreign key is a 4 byte integer), another is to deal with exceptional values. For example, if you use a smart key - such as a ddmmyyyy date string - you have an issue if you want to record facts for which the date value is unknown, or not applicable. These issues are avoided by the use of meaningless surrogate keys.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources