Which DataType to use - 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

SQL Server 2008 Forums

SQL Server Administration (2008)

Which DataType to use

Author

Topic

James Niven
Starting Member

17 Posts

Posted - 2014-11-18 : 14:43:33

Hello,
Which is the correct data type to use for storing 4 digit numbers, example GMT or UTC time formats?

I want to store 0100, or 0001, any number beginning with zero.
I am having issues finding the correct data type.

Can someone please provide an answer?

=======================
James Niven
Austin, Texas
=======================

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 14:49:03

char(4), for the 0100 or 0001 examples. But if this is a time value, then why not just use the time data type?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

James Niven
Starting Member

17 Posts

Posted - 2014-11-18 : 15:10:43

tkizer,

Thanks for the reply, but neither of these data types did not give me what I need, any other suggestions?

=======================
James Niven
Austin, Texas
=======================

James Niven
Starting Member

17 Posts

Posted - 2014-11-18 : 15:12:27

I might add, I have tried, Char(4), Text, Time, varchar, no luck

=======================
James Niven
Austin, Texas
=======================

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 15:23:41

I'm not understanding the issue then. Are you trying to insert the value 1 and have it padded as 0001? If so, you can use the RIGHT function: RIGHT('0000' + value, 4)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

James Niven
Starting Member

17 Posts

Posted - 2014-11-18 : 15:26:39

I am sorry, Padded?

=======================
James Niven
Austin, Texas
=======================

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 15:30:16

quote:
Originally posted by James Niven

I am sorry, Padded?

Meaning do you want the zeros to be added so that it is 4 digits even if less than 4 are provided

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

James Niven
Starting Member

17 Posts

Posted - 2014-11-18 : 15:32:54

Ok, It will always be 4 digits added.

=======================
James Niven
Austin, Texas
=======================

James Niven
Starting Member

17 Posts

Posted - 2014-11-18 : 15:34:11

How do I use the following and where?

"If so, you can use the RIGHT function: RIGHT('0000' + value, 4)"

=======================
James Niven
Austin, Texas
=======================

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 15:38:51

That's where it gets tricky. I would modify the application or stored procedure so that it sends in the correct format and then would add a check constraint to the column so that the insert or update fails if not provided in the right format. You could use an INSTEAD OF trigger so that you don't have to modify the application/stored procedure.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

James Niven
Starting Member

17 Posts

Posted - 2014-11-18 : 15:45:59

Thanks, but I am a newbie and don't quite follow what I need to do.
I have an excel 2010 spreadsheet with rows of data, the 2 columns with the 4 digit UTC are showing in the correct format. I have created an Visual Studio application that is linked to sql server database and I import the spreadsheet data via the application into the table.
This is my process, what do I need to do to update my process, and I sorry, can you dumb it down for me to understand.

=======================
James Niven
Austin, Texas
=======================

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 15:48:17

It sounds like you need to update the VS application. If the spreadsheet has the data in the correct format and CHAR(4) or even VARCHAR(4) are being used for the data type, then something is wrong with the VB application. I suspect the VB application is using the wrong data type.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

James Niven
Starting Member

17 Posts

Posted - 2014-11-18 : 15:54:22

ok, that my be something I can look at.
But......I tried typing data straight into the database table with data type as char(4) and I still don't get the correct results I want, so I really think its back to the data type.

=======================
James Niven
Austin, Texas
=======================

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 15:56:42

CHAR(4) works fine:

CREATE TABLE t1(Column1 CHAR(4))
INSERT INTO t1 VALUES('0001')
INSERT INTO t1 VALUES('1')
INSERT INTO t1 VALUES('001')
INSERT INTO t1 VALUES('0100')

SELECT * FROM t1

DROP TABLE t1

Note that it will hold whatever you insert into it. If you don't pad it with the zeroes, it won't store it that way unless you've added an INSTEAD OF trigger.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

James Niven
Starting Member

17 Posts

Posted - 2014-11-18 : 16:06:20

Ok, great, thanks tkizer, that create table worked fine and displays correctly.
So, I need to look at my application as you suggested. I am wondering if there is something within the import script changing something?

=======================
James Niven
Austin, Texas
=======================

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 16:09:12

I don't have experience with VB to be able to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

James Niven
Starting Member

17 Posts

Posted - 2014-11-18 : 16:12:16

Thanks, I understand!!

=======================
James Niven
Austin, Texas
=======================

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources