Format date in a database results region

[Guest]

I am retrieving a date field from an Access database that is formatted as
medium data in the database table but it is displayed as US short date in the
results region. How can I get it displayed as a medium date in the database
results region on the web page?

 

[Jon Spivey]

Hi,

You can format the date any way you like using format, eg
select format(date(), 'mm/dd/yyyy') - 05/05/2005
select format(date(), 'dd mmm yyyy') - 05 May 2005
etc....

 

[Guest]

Thanks Jon

I have tried including this in my SQL select statement but it does not work.
Perhaps my sintax is wrong. My select statement is:
SELECT tblclub.ID, tblclub.club, tblfixture.date, tblfixture.fixname,
tblfixture.fixtype
FROM tblclub LEFT JOIN tblfixture ON tblclub.ID = tblfixture.ID
WHERE (((tblclub.club)=::club:) AND (((tblfixture.fixtype)=True))
Where should it go?

 

[Jon Spivey]

Hi,

Like this
SELECT C.ID, C.club, format([F.date],'dd mmm yyyy') AS TheDate, F.fixname,
F.fixtype
FROM tblclub C LEFT JOIN tblfixture F ON C.ID = F.ID
WHERE C.club=::club:: AND F.fixtype=True

This will return a field called TheDate in the format 5 May 2005 - I've just
tidied up the sql a bit as well to make it easier to read

 

[Guest]

Jon

Thanks very much. Works great

Hi,

Like this
SELECT C.ID, C.club, format([F.date],'dd mmm yyyy') AS TheDate, F.fixname,
F.fixtype
FROM tblclub C LEFT JOIN tblfixture F ON C.ID = F.ID
WHERE C.club=::club:: AND F.fixtype=True

This will return a field called TheDate in the format 5 May 2005 - I've just
tidied up the sql a bit as well to make it easier to read

--
Cheers,
Jon
Microsoft MVP

Thanks Jon

I have tried including this in my SQL select statement but it does not
work.
Perhaps my sintax is wrong. My select statement is:
SELECT tblclub.ID, tblclub.club, tblfixture.date, tblfixture.fixname,
tblfixture.fixtype
FROM tblclub LEFT JOIN tblfixture ON tblclub.ID = tblfixture.ID
WHERE (((tblclub.club)=::club:) AND (((tblfixture.fixtype)=True))
Where should it go?