invalid date time

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

invalid date time

Olivier Leprêtre
Hi,

 

I have an sqlite database with wrong information in a timestamp field. Using
System.Data.Sqlite, I want to get this information as a string inside a very
simple loop.

 

while (i < reader.FieldCount) {

txt = reader[i].ToString(); // or reader.GetString(i) or
Convert.ToString(reader.GetValue(i))

i++;

}

 

Problem is that if this works for all other fields (integer, varchar...) it
does not work for timestamp. Regardless reader function used, I get a
System.Format Exception Invalid DateTime when reading this field.

 

How can I avoid this internal cast and just get this information as a text
string, no matter its inside format ?

 

Thanks,

 

Olivier



---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: invalid date time

J Decker
On Sun, Feb 18, 2018 at 10:02 PM, Olivier Leprêtre <[hidden email]>
wrote:

> Hi,
>
>
>
> I have an sqlite database with wrong information in a timestamp field.
> Using
> System.Data.Sqlite, I want to get this information as a string inside a
> very
> simple loop.
>
>
>
> while (i < reader.FieldCount) {
>
> txt = reader[i].ToString(); // or reader.GetString(i) or
> Convert.ToString(reader.GetValue(i))
>
>

Should be able to get the value from the reader without any conversions
with dataReader[n]

....

odr = ObjectDataReader...

int ord = odr.GetOrdinal( PrimaryKey[i].ColumnName );
object o = odr[ord];


// GetSQLValue( Type t, Object o ) ....

if( t == typeof( DateTime ) )
return MakeDate( connection, Convert.ToDateTime( o ) ).ToString();

public static long MakeDate( DsnConnection dsn, DateTime dt )
{
// should check dsn for beavior NULL dsn being internal datatable
seelctable value
return ( dt.Year * 10000000000 + dt.Month * 100000000 + dt.Day * 1000000 +
dt.Hour * 10000 + dt.Minute * 100 + dt.Second );
}



> i++;
>
> }
>
>
>
> Problem is that if this works for all other fields (integer, varchar...) it
> does not work for timestamp. Regardless reader function used, I get a
> System.Format Exception Invalid DateTime when reading this field.
>
>
>
> How can I avoid this internal cast and just get this information as a text
> string, no matter its inside format ?
>
>
>
> Thanks,
>
>
>
> Olivier
>
>
>
> ---
> L'absence de virus dans ce courrier électronique a été vérifiée par le
> logiciel antivirus Avast.
> https://www.avast.com/antivirus
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: invalid date time

Olivier Leprêtre
Hi,

Thanks for this answer J but if I have only

SQLiteDataReader reader

object tmp = reader[i];

I get "string is not a valid DateTime" Exception on this line.

Olivier

-----Message d'origine-----
De : sqlite-users [mailto:[hidden email]] De la part de J Decker
Envoyé : lundi 19 février 2018 08:05
À : SQLite mailing list
Objet : Re: [sqlite] invalid date time

On Sun, Feb 18, 2018 at 10:02 PM, Olivier Leprêtre <[hidden email]>
wrote:

> Hi,
>
>
>
> I have an sqlite database with wrong information in a timestamp field.
> Using
> System.Data.Sqlite, I want to get this information as a string inside
> a very simple loop.
>
>
>
> while (i < reader.FieldCount) {
>
> txt = reader[i].ToString(); // or reader.GetString(i) or
> Convert.ToString(reader.GetValue(i))
>
>

Should be able to get the value from the reader without any conversions with dataReader[n]

....

odr = ObjectDataReader...

int ord = odr.GetOrdinal( PrimaryKey[i].ColumnName ); object o = odr[ord];


// GetSQLValue( Type t, Object o ) ....

if( t == typeof( DateTime ) )
return MakeDate( connection, Convert.ToDateTime( o ) ).ToString();

public static long MakeDate( DsnConnection dsn, DateTime dt ) { // should check dsn for beavior NULL dsn being internal datatable seelctable value return ( dt.Year * 10000000000 + dt.Month * 100000000 + dt.Day * 1000000 + dt.Hour * 10000 + dt.Minute * 100 + dt.Second ); }



> i++;
>
> }
>
>
>
> Problem is that if this works for all other fields (integer,
> varchar...) it does not work for timestamp. Regardless reader function
> used, I get a System.Format Exception Invalid DateTime when reading this field.
>
>
>
> How can I avoid this internal cast and just get this information as a
> text string, no matter its inside format ?
>
>
>
> Thanks,
>
>
>
> Olivier
>
>
>
> ---
> L'absence de virus dans ce courrier électronique a été vérifiée par le
> logiciel antivirus Avast.
> https://www.avast.com/antivirus
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: invalid date time

Cezary H. Noweta
In reply to this post by Olivier Leprêtre
Hello,

On 2018-02-19 07:02, Olivier Leprêtre wrote:

> I have an sqlite database with wrong information in a timestamp field. Using
> System.Data.Sqlite, I want to get this information as a string inside a very
> simple loop.
>
> while (i < reader.FieldCount) {
>
> txt = reader[i].ToString(); // or reader.GetString(i) or
> Convert.ToString(reader.GetValue(i))
>
> i++;
>
> }
>
> Problem is that if this works for all other fields (integer, varchar...) it
> does not work for timestamp. Regardless reader function used, I get a
> System.Format Exception Invalid DateTime when reading this field.
>
> How can I avoid this internal cast and just get this information as a text
> string, no matter its inside format ?

Use ``Flags=GetAllAsText'' when creating a SQLiteConnection.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: invalid date time

Joe Mistachkin-3

Cezary H. Noweta wrote:
>
> Use ``Flags=GetAllAsText'' when creating a SQLiteConnection.
>

Excellent suggestion.  Alternatively, you could use the GetString
method on the SQLiteDataReader class.

The GetValue method, by design, refers to the table schema so that
it can convert the requested value into the declared type (which I
assume is DateTime in this case).

--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: invalid date time

Cezary H. Noweta
Hello,

On 2018-02-19 13:08, Joe Mistachkin wrote:
> Cezary H. Noweta wrote:
>> Use ``Flags=GetAllAsText'' when creating a SQLiteConnection.
>
> Excellent suggestion.  Alternatively, you could use the GetString
> method on the SQLiteDataReader class.

Indeed, however OP posted that ``GetString()'' does not work in his
case, for unknown reason. Maybe a bit different approach will help.

> The GetValue method, by design, refers to the table schema so that
> it can convert the requested value into the declared type (which I
> assume is DateTime in this case).

Yes and moreover, mentioned by you ``GetString()'' (which would be most
concise and useful in this case as it does not influence on all
connection) omits this schemata and should work in case of corrupted
``datetime'' fields/columns. I'm not sure where does the problem lie, if
``GetString()'' does not work. Additional info from OP would help.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: invalid date time

Olivier Leprêtre
In reply to this post by Cezary H. Noweta
Thanks to all for your answers, special thanks to Cezary :

Use `Flags=GetAllAsText' when creating a SQLiteConnection is the right answer.

After, I used reader.GetValue(i).ToString() to get all values as strings, regardless their type.

Have a good day,

Olivier
-----Message d'origine-----
De : sqlite-users [mailto:[hidden email]] De la part de Cezary H. Noweta
Envoyé : lundi 19 février 2018 13:03
À : SQLite mailing list
Objet : Re: [sqlite] invalid date time

Hello,

On 2018-02-19 07:02, Olivier Leprêtre wrote:

> I have an sqlite database with wrong information in a timestamp field.
> Using System.Data.Sqlite, I want to get this information as a string
> inside a very simple loop.
>
> while (i < reader.FieldCount) {
>
> txt = reader[i].ToString(); // or reader.GetString(i) or
> Convert.ToString(reader.GetValue(i))
>
> i++;
>
> }
>
> Problem is that if this works for all other fields (integer,
> varchar...) it does not work for timestamp. Regardless reader function
> used, I get a System.Format Exception Invalid DateTime when reading this field.
>
> How can I avoid this internal cast and just get this information as a
> text string, no matter its inside format ?

Use ``Flags=GetAllAsText'' when creating a SQLiteConnection.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users