Re: FN-FORUM: ASP suckery

date posted 5th January 2007 12:40

> I'm more of a PHP bod, but needs must and all that, so I'm looking at an
> ASP
> (classic) site for a client. I hasten to add, that I didn't write this
> site
> myself, and I have no idea if it's the APS that sucks, or the original
> programmer who sucks, but clearly something isn't right.
>
> The code is getting some data from SQL server database - dates in this
> instance.
>
> Here's the relevant code, the bits I've removed are just to do with the
> other fields that aren't relevant.
>
> szSQL = "SELECT * "
> szSQL = szSQL & "FROM SPJobs "
> szSQL = szSQL & "WHERE ( SPJobs.daCreateDate >= '" & Month(strFromDate) &
> "/" & Day(strFromDate) & "/" & Year(strFromDate) & " 00:00' "
> szSQL = szSQL & "AND SPJobs.daCreateDate Day(strToDate) & "/" & Year(strToDate) & " 23:59' ) "
>
>
>
> szSQL = szSQL & "ORDER BY daCreateDate,SPCompanies.strCompanyName "
>
> Response.Write "SQL: " & szSQL & ""
> Set oRs = oConn.Execute( szSQL, lngRecs, adCmdText )
> if oRs.BOF and oRs.EOF then
> %>
>
>  
>  There are no items. 
>  
>
> else
> oRs.MoveFirst
> Do While Not oRs.EOF
> nJobID = oRs.Fields("nJobID")
> daCreateDate = oRs.Fields("daCreateDate")
> daDespatchDate = oRs.Fields("daDespatchDate")
>
>
> strCreateDate = Day(daCreateDate) & "/" & Month(daCreateDate) & "/" &
> Year(daCreateDate)
> strDespatchDate = Day(daDespatchDate) & "/" & Month(daDespatchDate) & "/"
> &
> Year(daDespatchDate)
>
> %>
>
>
>
>
>
> oRs.MoveNext
> Loop
> end if
> %>
>
>
> The problem is that while daCreateDate works fine and comes out in the
> proper format. daDespatchDate always comes out as 30/12/1899 ( I suspect
> this is a default date for date objects ?) I note the format is correct,
> but
> the date isn't. Both columns do have data, both are stored as the same
> kind
> of date format.
>
> Any clues.
>

This is more of a MS-SQL issue I suspect.

Have you selected all rows and iterated through writing daDespatchdate? This
would determine whether the data in this column is good or not.

Personally for date comparisons with MS-SQL I convert all dates to 'd
monthname yyyy'
format before comparison - this ensures that whatever the server date
settings are you will make the correct comparison.

John