Odd date display problem

[Naoise]

I have a table with a field of type datetime.
tblFoo
-------------------------
FooDate [smalldatetime]

Sample data
tblFoo
-------------------------
FooDate
2007-05-02 00:00:00

select datepart(dd,FooDate) from tblFoo = 2

select convert(datetime,FooDate,103) from tblFoo = 2007-05-02 00:00:00.000

Yet...

Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.ActiveConnection = MM_conn_asp_STRING
oRs.Source = "SELECT convert(datetime,FooDate,103) FROM tblFoo ORDER BY FooDate DESC"
oRs.CursorType = 0
oRs.CursorLocation = 2
oRs.LockType = 1
oRs.Open()

While (NOT oRs.EOF)
response.Write (oRs.Fields.Item("FooDate").Value & "<br/>")
oRs.MoveNext()
Wend
response.end

Gives me 5/2/2007

Any ideas 103 format is dd/mm/yy

 

[Naoise]

Beautifully formatted post and then it's all down to caching.

 

[gmmastros]

This is your problem.

SELECT convert(datetime,FooDate,103) FROM tblFoo ORDER BY FooDate DESC

You are converting this to a DateTime data type, which is already is. Instead, convert it to varchar, like this...

[tt][blue]
SELECT convert([!]varchar(10)[/!],FooDate,103) FROM tblFoo ORDER BY FooDate DESC
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

Yeah ASP will format the date how it wants to regardless of how you specify it in your SQL. You can get by this by converting the date to a char, so ASP will write it as a string instead of a date.

oRs.Source = "SELECT convert(char(9?), convert(datetime,FooDate,103)) FROM tblFoo ORDER BY FooDate DESC"

<.

 

[monksnake]

Yeah George I do that almost everyday, convert a char to a date char, and still I kept the datetime conversion in my post, what's wrong with me???

<.

 

[ChrisHirst]

FormatDateTime()

Session.LCID

UK date format is 2057

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.