DATE in Text

[Ghozer]

I have a Database, have a table called MATCHES and a matches_date column,

in that Column you can have a text string that reads, "week 1" or "week 2" etc, or you can have a date in "DD/MM/YY HH:MM" Format, these are set to store as text...

I have 2 pages, one is FIXTURES.asp and the other is RESULTS.asp What i am wanting is...

IF it's a date, It's a result.. IF it's not - then it's a fixture... how do i do this...

I seperated the pages..

here is my read for FIXTURES....
-----------------------------------------
<%
Dim main_results
Dim main_results_numRows

Set main_results = Server.CreateObject("ADODB.Recordset")
main_results.ActiveConnection = MM_csgn_dbconx_STRING
main_results.Source = "SELECT * FROM MATCHES WHERE matches_date > Now() ORDER BY ID DESC"
main_results.CursorType = 0
main_results.CursorLocation = 2
main_results.LockType = 1
main_results.Open()

main_results_numRows = 0
%>
-----------------------------------------
and here is my read for RESULTS....
-----------------------------------------
<%
Dim main_results
Dim main_results_numRows

Set main_results = Server.CreateObject("ADODB.Recordset")
main_results.ActiveConnection = MM_csgn_dbconx_STRING
main_results.Source = "SELECT * FROM MATCHES WHERE matches_date < DATETIME() ORDER BY ID DESC"
main_results.CursorType = 0
main_results.CursorLocation = 2
main_results.LockType = 1
main_results.Open()

main_results_numRows = 0
%>

[Ghozer]

!EDIT!
I forgot to mention it was in VB Script :)
!EDIT!

[txbakers]

Well, since the field is a text field I doubt you can use the matches_date < now() criteria in your where clause.

You can try cdate(matches_date) < now() but I don't think that will work either.

You might be better off having two fields, one for a date format, the other for a text.

Then, you can use an IIF statement to select which page to use. IIF ( isnull(matches(date_field)) , show page 1, show page2)

Something like that might work.

[Ghozer]

the cdate( diddnt work, and i dont think the IF will either..

the problem i have is that on FIXTURES.asp - it needs to read the column, and IF there's a date there, ignore it, IF there's text there "week X" - Place the information for that row, on the page..

the same with results, but in reverse, IF there's text there, Ignore it, If there's a Date there "DD/MM/YY HH:MM" Place that row on the page...

<snip>

you can see the problem > its showing them ALL on Fixtures and not results

[edited by: Xoc at 11:36 pm (utc) on Sep. 3, 2003]
[edit reason] removed url [/edit]

[Ghozer]

well, I'm guessing no-one knows, Thanks any ways...

[killroy]

MySQL can easily use dates in text format. You just have to comply with the standards.

first you have to convert the DD/MM/YY HH:MM to YYYY-MM-DD HH:MM:SS

best is to use the substring function.

since the week bits won't convert you gotta filter them out FIRST

either with a IF(LOCATE("week",field),,)
or with a WHERE field NOT LIKE "%week%"

If you can give me more details (the DESCRIBE of the table)

I could complete the queries, but this should give you enough to work on.

SN

[Ghozer]

thing is, it's not MySQL, it's an MSODB - Standard Jet database

[killroy]

well as long as it's ISO SQL compatible...

SN

[Ghozer]

I got it sorted,

I changed the SELECT Function, then put in a little check..
the function is now.....
----------
main_results.Source = "SELECT * FROM MATCHES ORDER BY ID DESC"
----------
and i use...
---------- <%
While ((Repeat8__numRows <> 0) AND (NOT main_results.EOF))
If Not IsDate(main_results.Fields.Item("matches_date").Value) Then
%>
----------

to check if it's date or not, IF it's not, then it's a fixture, if it IS then it's a result :) got it sorted, thanx for your help people :) - some times the simplest things are the ****y ones..