Ordering by date ???

[TomT]

I have created a DB in Access 2000.

The Date Field (clientdate) is currently a MEMO field.

The date is being entered as:

10 June 2004
21 January 2005
09 September 2004

How would I display this in Date order ?

I have tried changing the field type to Date/Time, and setting it to long
date format.

I'm using an SQL Query to select Name, Address and Date from the DB (this
works)..

I've added 'Order By clientdate' this works but the date is displayed as:

10/06/2004
09/09/2004
21/01/2005

Not the long date format !

How do I get it to order and display the correct dates.

I'm displaying it in ASP using <%=rsInfo("clientdate")%>

Thanks Tom

[Aaron [SQL Server MVP]]

> The Date Field (clientdate) is currently a MEMO field.

Uh, why?

> The date is being entered as:
>
> 10 June 2004
> 21 January 2005
> 09 September 2004

Stop letting people enter dates this way!

> I have tried changing the field type to Date/Time, and setting it to long
> date format.

And, what happened?

> I've added 'Order By clientdate' this works but the date is displayed as:

Did you try ORDER BY CDATE(ClientDate)?

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[TomT]

On Mon, 2 Aug 2004 15:40:04 -0400, Aaron [SQL Server MVP] wrote:

>> The Date Field (clientdate) is currently a MEMO field.

>
> Uh, why?

This was not going to be search or ordered by, and the date is being
entered from a popup calendar.

>> The date is being entered as:
>>
>> 10 June 2004
>> 21 January 2005
>> 09 September 2004

>
> Stop letting people enter dates this way!

This is the format the popup calendar uses, It's also easier to read, than
10/10/2004

>

>> I have tried changing the field type to Date/Time, and setting it to long
>> date format.

>
> And, what happened?

This seems to work, I can edit or change the date OK, but when I display it
I get 10/10/04 not 10 October 2004

>

>> I've added 'Order By clientdate' this works but the date is displayed as:

>
> Did you try ORDER BY CDATE(ClientDate)?

?? what does that do ?

Thanks

[Aaron [SQL Server MVP]]

> This was not going to be search or ordered by, and the date is being

> entered from a popup calendar.

You should start using a date type column, because obviously that's what
you're storing and how you're using it.

Then, you don't have to worry about the storage format. You pass in
YYYY-MM-DD, Access never gets confused about whether 10/12/2004 is December
10 or October 12, and all of your date calculations and sorting suddenly
just work.

> This is the format the popup calendar uses, It's also easier to read, than
> 10/10/2004

The point is once you get the date STORED in the database correctly, you can
display it any way you like.

> This seems to work, I can edit or change the date OK, but when I display

it

> I get 10/10/04 not 10 October 2004

Did you try <%=formatdatetime(rs("datecolumn"), 1)%> or creating your own
display function? I think you are missing the point that storage and
display are two completely separate issues.

> > Did you try ORDER BY CDATE(ClientDate)?

> ?? what does that do ?

CDate() converts the value to a date, so that is how it is sorted, instead
of what you have, which is a string (so of course 10/02/1954 comes after
08/02/2004). But for the long run, I strongly recommend revisiting the
table design before you start littering your queries with kludges that
shouldn't have to be there in the first place.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[TomT]

On Mon, 2 Aug 2004 16:10:41 -0400, Aaron [SQL Server MVP] wrote:


Thanks for your help, I'm beginning to understand...

>>> Did you try ORDER BY CDATE(ClientDate)?

>> ?? what does that do ?

>
> CDate() converts the value to a date, so that is how it is sorted, instead
> of what you have, which is a string (so of course 10/02/1954 comes after
> 08/02/2004). But for the long run, I strongly recommend revisiting the
> table design before you start littering your queries with kludges that
> shouldn't have to be there in the first place.

I have let the database and fields as they were, and tried 'order by
CDATE(clientdate)'

This has resulted in the information, being order by the date.

Thanks

[Steven Burn]

Use the "order by" clause and format the date using FormatDateTime()

<%=FormatDateTime(rsInfo("clientdate"), 1)%>

--

Regards

Steven Burn
Ur I.T. Mate Group
[url]www.it-mate.co.uk[/url]

Keeping it FREE!


"TomT" <tomt@adslweb.co.uk> wrote in message
news:1uodba9fgl3lv.10f58spytruv$.dlg@40tude.net...

> I have created a DB in Access 2000.
>
> The Date Field (clientdate) is currently a MEMO field.
>
> The date is being entered as:
>
> 10 June 2004
> 21 January 2005
> 09 September 2004
>
> How would I display this in Date order ?
>
> I have tried changing the field type to Date/Time, and setting it to long
> date format.
>
> I'm using an SQL Query to select Name, Address and Date from the DB (this
> works)..
>
> I've added 'Order By clientdate' this works but the date is displayed as:
>
> 10/06/2004
> 09/09/2004
> 21/01/2005
>
> Not the long date format !
>
> How do I get it to order and display the correct dates.
>
> I'm displaying it in ASP using <%=rsInfo("clientdate")%>
>
> Thanks Tom

[Aaron [SQL Server MVP]]

> Use the "order by" clause

He's already doing that! The problem is that the data is not a date, it is
just character... so 10 November 1995 comes before 12 December 1992.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[TomT]

OK,

So if I change the field, from Memo to Date/Time, with Long Format.

And use Order By CDATE(clientdate) Would that be right ???

Would I get the date outputted as 10 October 2004 or 10/10/04 ?

How would I fix that ?

Thanks for your help !




"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:exUAsaNeEHA.1656@TK2MSFTNGP09.phx.gbl...

> > Use the "order by" clause

>
> He's already doing that! The problem is that the data is not a date, it

is

> just character... so 10 November 1995 comes before 12 December 1992.
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)
>
>

[dlbjr]

Do not use "DATE" as a field name. I used SDATE for the field name.

SELECT CDate([SDATE]) AS CDATE, NAME, ADDRESS
FROM TableName
ORDER BY CDate([SDATE]);


dlbjr
Pleading sagacious indoctrination!

[TomT]

I'm using 'clientdate' as the field name..

I'll change the field from MEMO to Date/Time

I just need to know how to display it as 10 October 2004 not 10/10/04

Again many thanks



"dlbjr" <oops@iforgot.com> wrote in message
news:e$CaT$NeEHA.1048@tk2msftngp13.phx.gbl...

> Do not use "DATE" as a field name. I used SDATE for the field name.
>
> SELECT CDate([SDATE]) AS CDATE, NAME, ADDRESS
> FROM TableName
> ORDER BY CDate([SDATE]);
>
>
> dlbjr
> Pleading sagacious indoctrination!
>
>

[Mark Schupp]

If you make clientdate a date/time column then you can just order by
clientdate (CDate not needed any more).

Default display format will depend on system settings. It is usually safest
to format the date with a function you control. What data format does you
popup calendar like. Mine uses yyyymmddThh:mm:ss so I use the following to
format the dates for it:

Public Function XMLDate(ByVal dtIn)

Dim strSeconds

XMLDate = ""

If Not IsNull(dtIn) Then

If IsDate(dtIn) Then

dtIn = CDate(dtIn)

strSeconds = CStr(Second(dtIn))
If Len(strSeconds) < 2 Then strSeconds = "0" & strSeconds

XMLDate = Year(dtIn) & "-" & _
Right("0" & Month(dtIn), 2) & "-" & _
Right("0" & Day(dtIn), 2) & _
"T" & Right("0" & Hour(dtIn), 2) & ":" & _
Right("0" & Minute(dtIn), 2) & ":" & _
strSeconds

End If

End If

End Function



--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"TomT" <tomt@adslweb.co.uk> wrote in message
news:410eb955$0$6450$cc9e4d1f@news-text.dial.pipex.com...

> OK,
>
> So if I change the field, from Memo to Date/Time, with Long Format.
>
> And use Order By CDATE(clientdate) Would that be right ???
>
> Would I get the date outputted as 10 October 2004 or 10/10/04 ?
>
> How would I fix that ?
>
> Thanks for your help !
>
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> news:exUAsaNeEHA.1656@TK2MSFTNGP09.phx.gbl...

> > > Use the "order by" clause

> >
> > He's already doing that! The problem is that the data is not a date, it

> is

> > just character... so 10 November 1995 comes before 12 December 1992.
> >
> > --
> > [url]http://www.aspfaq.com/[/url]
> > (Reverse address to reply.)
> >
> >

>
>

[Aaron [SQL Server MVP]]

> I just need to know how to display it as 10 October 2004 not 10/10/04

Once again. Your display has ABSOLUTELY NOTHING to do with the storage.
Let me repeat: ABSOLUTELY NOTHING!!!!!! In case it's not clear, storage IS
NOT equal to presentation! Once you get the *date* out of the database, you
can format it in any way you like. When you're inserting it into HTML, it
is no longer a date, it is just a string. But you can take advantage of
date functions since it LOOKS like a string to VBScript.

You can see some examples here, though not the *exact* format you want:
[url]http://www.aspfaq.com/2313[/url]

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Marlo Brandon]

First off, you are right to get it into time/date format in Access, rather
than memo.

Second, now that the data sorts correctly, you have to display it correctly.

In the ASP page, use the formatdatetime function
formatdatetime(dateFromRecordset,1)

Fool around with the numbers till you a display format that you like.

Since none of the number choices yeild exactly what you asked for, you'll
have to write a custom little function using
datepart for month, day and year





"TomT" <tomt@adslweb.co.uk> wrote in message
news:1uodba9fgl3lv.10f58spytruv$.dlg@40tude.net...

> I have created a DB in Access 2000.
>
> The Date Field (clientdate) is currently a MEMO field.
>
> The date is being entered as:
>
> 10 June 2004
> 21 January 2005
> 09 September 2004
>
> How would I display this in Date order ?
>
> I have tried changing the field type to Date/Time, and setting it to long
> date format.
>
> I'm using an SQL Query to select Name, Address and Date from the DB (this
> works)..
>
> I've added 'Order By clientdate' this works but the date is displayed as:
>
> 10/06/2004
> 09/09/2004
> 21/01/2005
>
> Not the long date format !
>
> How do I get it to order and display the correct dates.
>
> I'm displaying it in ASP using <%=rsInfo("clientdate")%>
>
> Thanks Tom

[Bã§TãRÐ]

You can change the out put using ASP by putting the date into this statement

FormatDateTime(rsInfo("clientdate"), 1)

This will print the date out like

Tuesday, August 03, 2004





On Mon, 2 Aug 2004 20:32:55 +0100, TomT <tomt@adslweb.co.uk> wrote:

>I have created a DB in Access 2000.
>
>The Date Field (clientdate) is currently a MEMO field.
>
>The date is being entered as:
>
>10 June 2004
>21 January 2005
>09 September 2004
>
>How would I display this in Date order ?
>
>I have tried changing the field type to Date/Time, and setting it to long
>date format.
>
>I'm using an SQL Query to select Name, Address and Date from the DB (this
>works)..
>
>I've added 'Order By clientdate' this works but the date is displayed as:
>
>10/06/2004
>09/09/2004
>21/01/2005
>
>Not the long date format !
>
>How do I get it to order and display the correct dates.
>
>I'm displaying it in ASP using <%=rsInfo("clientdate")%>
>
>Thanks Tom

[Bob Barrows [MVP]]

Bã§TãRÐ wrote:

> You can change the out put using ASP by putting the date into this
> statement
>
> FormatDateTime(rsInfo("clientdate"), 1)
>
> This will print the date out like
>
> Tuesday, August 03, 2004
>

Only if the computer's regional settings result in this format.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[TomT]

On Tue, 3 Aug 2004 00:08:22 -0400, Aaron [SQL Server MVP] wrote:

>> I just need to know how to display it as 10 October 2004 not 10/10/04

>
> Once again. Your display has ABSOLUTELY NOTHING to do with the storage.
> Let me repeat: ABSOLUTELY NOTHING!!!!!! In case it's not clear, storage IS
> NOT equal to presentation! Once you get the *date* out of the database, you
> can format it in any way you like. When you're inserting it into HTML, it
> is no longer a date, it is just a string. But you can take advantage of
> date functions since it LOOKS like a string to VBScript.
>
> You can see some examples here, though not the *exact* format you want:
> [url]http://www.aspfaq.com/2313[/url]

I get what you mean... Honestly...

The information is held in clientdate, and now looks like this
dd/mm/yyyy
05/10/2004

I'm still struggling to get the HTML to display it as 05 October 2004

When my PopUp Calendar, write the info into the text box, it displays as 05
October 2004, until I write it to the DB, then its gets displayed as
05/10/2004


Some please help ?

[Aaron [SQL Server MVP]]

> October 2004, until I write it to the DB, then its gets displayed as

> 05/10/2004

In the DB? Why do you care how "its gets displayed" in the DB? You're
still confusing storage and presentation, I think.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[TomT]

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:ORajHHYeEHA.332@TK2MSFTNGP09.phx.gbl...

> > October 2004, until I write it to the DB, then its gets displayed as
> > 05/10/2004

>
> In the DB? Why do you care how "its gets displayed" in the DB? You're
> still confusing storage and presentation, I think.
>

Aaron,I don't care about what is in the DB, only what is shown on screen and
so I can order by the date.

So forgetting how it is in the DB, how do I get it to display correctly on
screen ??

Thanks again

[Mark Schupp]

If it is stored as a date internally then create a custom date formatting
function to get the display format you want:

function dateout (yourdate)
dateout = datepart( "d",yourdate) & " "
select case datepart("m",yourdate)
case 1:
dateout = dateout & "January"
case 2
dateout = dateout & "February"
...
end select

dateout = dateout & datepart("yyyy", yourdate )
end function

Or you can try to get the default locale settings on the server to match the
format you want.

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"TomT" <tomt@adslweb.co.uk> wrote in message
news:410ff5ca$0$6446$cc9e4d1f@news-text.dial.pipex.com...

>
> "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> news:ORajHHYeEHA.332@TK2MSFTNGP09.phx.gbl...

> > > October 2004, until I write it to the DB, then its gets displayed as
> > > 05/10/2004

> >
> > In the DB? Why do you care how "its gets displayed" in the DB? You're
> > still confusing storage and presentation, I think.
> >

>
> Aaron,I don't care about what is in the DB, only what is shown on screen

and

> so I can order by the date.
>
> So forgetting how it is in the DB, how do I get it to display correctly on
> screen ??
>
> Thanks again
>
>
>