RE: [PHP-DB] Trouble retrieving DATETIME info from MSSQL - resolved

2001-10-02 Thread Matthew DeChant 

Dates in MSSQL are stored as:
mon dd  hh:miAM (or PM)

and ODBC converts them to:
-mm-dd hh:mi:ss.mmm(24h)

so:
select * from Internetlog where convert(varchar, LogTime, 121) like
'%2001-10-02%'

-Original Message-
From: Matthew DeChant [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 01, 2001 2:26 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Trouble retrieving DATETIME info from MSSQL


Hello,

I am having trouble getting data from a DATETIME field in MSSQL 7.0.

The data in a general select query look like this:

2001-09-25 14:23:17.000


I can do this in MSSQL:

select * from INTERNETLOG where Datetime like %2001%

but not this:

select * from INTERNETLOG where Datetime like %2001/09/25%
or
select * from INTERNETLOG where Datetime like %20010925%
or
select * from INTERNETLOG where Datetime like %2001/09%
or
select * from INTERNETLOG where Datetime like %14:23%



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

[PHP-DB] Trouble retrieving DATETIME info from MSSQL

2001-10-01 Thread Matthew DeChant 

Hello,

I am having trouble getting data from a DATETIME field in MSSQL 7.0.

The data in a general select query look like this:

2001-09-25 14:23:17.000


I can do this in MSSQL:

select * from INTERNETLOG where Datetime like %2001%

but not this:

select * from INTERNETLOG where Datetime like %2001/09/25%
or
select * from INTERNETLOG where Datetime like %20010925%
or
select * from INTERNETLOG where Datetime like %2001/09%
or
select * from INTERNETLOG where Datetime like %14:23%

I think it has to do with converting the data type but that's where I'm
stuck.

from the SQL manual:

Pattern Matching with LIKE
It is recommended that LIKE be used when you search for datetime values,
because datetime entries can contain a variety of dateparts. For example, if
you insert the value 19981231 9:20 into a column named arrival_time, the
clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20
string because SQL Server converts it to Jan 1, 1900 9:20AM. However, the
clause WHERE arrival_time LIKE ‘%9:20%’ does find it.

I've seen a couple of initial questions asked about the same problem but not
the answer. Is this a PHP/ODBC issue or just MSSQL?

-m


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]