leading zero removed from varchar(6)

Hi

I have a table where a field is described as checkin_time varchar(6). The
value might have a leading zero (0730) . When I SELECT checkin_time the
leading zero disappears. Any ideas why?

Regards
John

> I have a table where a field is described as checkin_time varchar(6). The
> value might have a leading zero (0730) . When I SELECT checkin_time the
> leading zero disappears. Any ideas why?

You have to use quotes for strings (single or double, dipends on you
sql_mode setting):

mysql> create table t(a varchar(6));
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t values (0730);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t values ('0730');
Query OK, 1 row affected (0.04 sec)

mysql> select * from t;
+------+
| a |
+------+
| 730 |
| 0730 |
+------+
2 rows in set (0.00 sec)

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t values ("0730");
Query OK, 1 row affected (0.37 sec)

mysql> set sql_mode=ANSI_QUOTES;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values ("0730");
ERROR 1054 (42S22): Unknown column '0730' in 'field list'


Regards
Dimitre

John wrote:
> Hi
>
> I have a table where a field is described as checkin_time varchar(6). The
> value might have a leading zero (0730) . When I SELECT checkin_time the
> leading zero disappears. Any ideas why?
>
> Regards
> John
>
>

John,

It the leading zero disappearing when you select the time, or when you
display it? What language are you using? And what's your code?

Languages which don't have strong typing may take this as a numeric
value and drop leading zeros.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Hi folks

OK, I am using Perl, where $x can be any type of variable (string numeric
etc)
I need to recheck that there are quotes surrounding the "0745".
Will be back.

Regards
John

John wrote:

> Hi folks
>
> OK, I am using Perl, where $x can be any type of variable (string numeric
> etc)
> I need to recheck that there are quotes surrounding the "0745".
> Will be back.
>
> Regards
> John


In Perl, if $x contain "0745" and you do any thing numerical with $x, it
will become 745.

$ perl -e '$x = "0745";print $x,"\n"'
0745
$ perl -e '$x = "0745";$x+=0;print $x,"\n"'
745


--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png

Brian Wakem wrote:
> John wrote:
>
> > Hi folks
> >
> > OK, I am using Perl, where $x can be any type of variable (string numeric
> > etc)
> > I need to recheck that there are quotes surrounding the "0745".
> > Will be back.
> >
> > Regards
> > John
>
>
> In Perl, if $x contain "0745" and you do any thing numerical with $x, it
> will become 745.
>
> $ perl -e '$x = "0745";print $x,"\n"'
> 0745
> $ perl -e '$x = "0745";$x+=0;print $x,"\n"'
> 745

I am just taking a stab in the dark here, but would it make any
difference using single quotes and not double quotes?

Daz wrote:

>
> Brian Wakem wrote:
>> John wrote:
>>
>> > Hi folks
>> >
>> > OK, I am using Perl, where $x can be any type of variable (string
>> > numeric etc)
>> > I need to recheck that there are quotes surrounding the "0745".
>> > Will be back.
>> >
>> > Regards
>> > John
>>
>>
>> In Perl, if $x contain "0745" and you do any thing numerical with $x, it
>> will become 745.
>>
>> $ perl -e '$x = "0745";print $x,"\n"'
>> 0745
>> $ perl -e '$x = "0745";$x+=0;print $x,"\n"'
>> 745
>
> I am just taking a stab in the dark here, but would it make any
> difference using single quotes and not double quotes?


No.


--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png