Data Format Consistency or query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Data Format Consistency or query?

Author

Topic

abuhassan

105 Posts

Posted - 2006-10-02 : 07:29:08

Hi

I have a table that stores the period and year as two seperate fields the problem that i have is that when the data is entered in the data base the period is sometime entered as 6 or 06, hence i have a table thaat looks like as follows:


Period Year
====== ====
6      2006
06     2005
12     2006
3      2005
2      2005
4      2005
04     2005
03     2005

when i currently query the information using the period i use the LIKE command in SQL which gives me the results but the problem is when the like is done with 2 as the period value it also retrieves the 12, 02, and 2, in that case the period 12 was not that was requested.

I was wondering is there a way to reformat the data so that all the data is in a consistent format?

Or is there a better way of quering the information in the current format?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-02 : 08:04:59

"I was wondering is there a way to reformat the data so that all the data is in a consistent format?"
The best way of-couse is to change the data type to int or decimial.

or use convert to convert to integer and compare


declare @year int,
        @period int
select *
from   table
where  Year = @year
and    convert(int, Period) = @period

KH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-02 : 09:30:32

Why did you use Varchar to store int value?
Always use proper datatype

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-02 : 09:39:19

Just in case you actually need them with the leading 0:

UPDATE mytable SET Period = RIGHT('00' + Period, 2)

But make sure you choose your datatypes properly! Int is probably far better for this kind of stuff...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-02 : 10:34:43

DateTime is nice too. you can always ignore the day portion.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-02 : 10:51:22

quote:
Originally posted by DonAtWork

DateTime is nice too. you can always ignore the day portion.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Datetime makes it a lot easier to do a from/to range query also.

CODO ERGO SUM

abuhassan

105 Posts

Posted - 2006-10-02 : 11:59:59

thanks

the reason why i chose var char was because the other people querying the database will be entering 02 or 04 no 2 or 4 but when but the people that are enetering the data and the other applications that are also entering the data seem to eneter it either 02 or 2. hence the problem.

Is there a way to query the results from the table without actually changing the format to a datetime or int?

because that will affect the other applications some of which i dont have any access to.

currently im using the following query


select * 

from
(
select '6' as Period, '2006' as [Year] union all
select  '06', '2005' union all
select  '12', '2006' union all
select  '3', '2005' union all
select  '4', '2005' union all
select  '04', '2005' union all
select  '2', '2005' union all
select  '02', '2005' union all
select  '03', '2005'


) t

where Period like '%2'

the results pull out 12 when i want only period 2/02 results is there a better way to query the result?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-02 : 12:10:05

Several people have already pointed out that the problem is with how your data is stored.

If you chose not to fix this problem, then you can expect to have to live with the query problems.

CODO ERGO SUM

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-03 : 02:49:06

[code]select *
from
(
select '6' as Period, '2006' as [Year] union all
select '06', '2005' union all
select '12', '2006' union all
select '3', '2005' union all
select '4', '2005' union all
select '04', '2005' union all
select '2', '2005' union all
select '02', '2005' union all
select '03', '2005'
) t
where CAST(Period AS int) = 2[/code]Just do the conversion at runtime...no need to change the physical storage of your data. Explicit conversion is not very performance friendly so you should consider other options if your table is large. One option could be to add another column of int datatype where you just insert the period column again.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 03:02:20

quote:
Originally posted by DonAtWork

DateTime is nice too. you can always ignore the day portion.

This assumes the PERIOD is equal to month, which it may not be.
However, I agree that period should be stored as tinyint, it the number of periods is never greater than 255 for a year.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 03:16:20

You can also add calculated columns for period and year as

create table #test (Period VARCHAR(2), [Year] VARCHAR(4))

insert #test
select '6', '2006' union all
select '06', '2005' union all
select '12', '2006' union all
select '3', '2005' union all
select '4', '2005' union all
select '04', '2005' union all
select '2', '2005' union all
select '02', '2005' union all
select '03', '2005'

alter table #test add per as CONVERT(TINYINT, Period)
alter table #test add yr as CONVERT(SMALLINT, [Year])

select * from #test
where per = 2 and yr = 2005

drop table #test

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-03 : 03:38:19

>> You can also add calculated columns
Good idea for a workaround

KH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 03:50:41

Thanks.
Yes, I thought so, if there are several systems working with this same table.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-03 : 05:19:43

Calculated columns cause some administrative problems.

For example, you cannot reindex or update statistics on a table with a calculated column with a maintenance plan. In effext, you cannot use a mintenance plan to reindex or update statistics for that databasem since you cannot select indibidual tables in a maintenance plan.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources