473,848 Members | 1,674 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

month date sorting

333 Contributor
Im using PHP report and I convert the month date into numbers but when I generate and publish it now displaying like this...

6 and so forth. How can I make it happen to sor it by correct ascending orders not sorting the first digits only.
Dec 1 '09 #1
9 2746
1,584 Recognized Expert Top Contributor
There's multiple solutions, what's your code like? is any of them coming from mysql?

Here's a good article from google:

The Numeric sort is what you want. Which is accomplished by adding zero, turning it into an number. You could also use intval()

Dec 1 '09 #2
333 Contributor
Here what's coming from MYSQL

Expand|Select|Wrap|Line Numbers
  1. Select  Date_Format(w.cal_date, '%c/%e/%Y') As Date,
  2.   Date_Format(w.cal_date, '%c') As Month,
  3.   Date_Format(w.cal_date, '%Y') As Year
  4. From web w
  5. Order By Year,  Month
Dec 1 '09 #3
333 Contributor
how can i convert the month(varchar) to interger?
Dec 1 '09 #4
1,584 Recognized Expert Top Contributor
This is how I thought you'd have it exactly too.

You don't need to convert anything, You just need to tell MySQL what to sort by. In this case you're telling it to sort by Month and Year, which are VARCHARS. If you say ORDER BY Year+0, Month+0 it should work for you.

You're basically telling mysql to automatically convert that number to do addition operation, then add zero. The result is an integer, thus when given to the sort engine, it will sort it like a number, instead of varchar.

Try that and let me know how it works out for ya,

Dec 1 '09 #5
1,584 Recognized Expert Top Contributor
May I ask, why aren't you just sorting by w.cal_date?

Dec 2 '09 #6
333 Contributor
I'm making a cross tab report or display that why I need to sort it by month. And it works when it was converted back to integer. Thanks for all your help Dan! your the BEST!
Dec 2 '09 #7
1,584 Recognized Expert Top Contributor
You're welcome, appreciate the compliment.

I didn't clarify my last question. Reason I asked is, sorting by date /is/ still sorting by month and year isn't it? You'll also sort by day, but if you don't need the date it won't hurt anything.

Here you're doing two operation when one PHP operation can do it: strtotime()

This way you have an integer unix epoch date value that you can plug in to the date() function and get out so much more than just the month number.


Dec 2 '09 #8
333 Contributor
Hey Dan,

Funny now i have another problem back to varchar and date type again... this time users select name of the month from the pull down menu and when I create a cross tab report as Month for the column it was sorted by character not by date as its equivalent month.

Now, can you tell me how to convert eg. January to 1?

Dec 3 '09 #9
1,584 Recognized Expert Top Contributor
you have to do that in code.

Write a generic function, put it somewhere accessible from your entire application, that has an array with all the months in it. The key is the month number, and the value is the name.

Why do they select January?

You're drop down menu should not have January as the option. Code works better with numbers than "Strings".

It should look like this: <option value="1">Janua ry</option>

So even though they select January, your program sees the number 1.

Hope that answered your question,

Dec 3 '09 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Dani | last post by:
Hi, I´m no superuser when it comes to MS Access. So I use MS Query to pull out info from an SQL database to Excel. I have a tabel containing different "titles" or "colums". One contains a date. When executing the query. And getting the data to Excel the date is in the standard date format (YYYY-MM-DD). I would like to get this data in a different format directly from/in the query.
by: bmoos1 | last post by:
I need to make a Report of all people starting in 2004 by "Date Requested". Dates are entered as mm/dd/yyyy. I have a query that has all the people that started in 2004, but there are numerous dates/days per month. I need to combine in my Report to show: Month: 01 # of starters 14 Type: BD 7 $1020 CC 7 $925
by: Tony Miller | last post by:
All I have an aggregate query using the function Month & Year on a datereceived field ie: TheYear: Year() TheMonth: Month() These are the group by fields to give me a Count on another field by year & month When I try to place a date filter 'Between x And y ' on an expression field
by: Katarina | last post by:
Hello all, I need help for sort date("Table"). How can I sort a month, then year? Thanks in advance, Catherine
by: Ante Perkovic | last post by:
Hi, How to declare datetime object and set it to my birthday, first or last day of this month or any other date. I can't find any examples in VS.NET help! BTW, what is the difference between date and datetime classes? Please, help
by: LimaCharlie | last post by:
Good Day to Everyone, Need help! I have this SQL table w/ fieldname "PayPeriod". Sample value of PayPeriod are SEP06-1, SEP06-2, OCT06-1, OCT06-2... Wherein the first 3 chars is the month, 06 is the year, 1 or 2 is the cutoff where 1 means the 2nd half of the previous month and 2 is the first half of the current month. Now my questions are: 1.) How can I select these values sorted according to date. Since this is a character data...
by: Jim | last post by:
I'm sure this has been asked before but I can't find any postings. I have a table that has weekly inspections for multiple buildings. What I need to do is break these down by the week of the month of my choosing. Let me explain; I can get my week numbers no problem. I can get the date from the week numbers no problem. What I haven't been able to do is put things together and make a simple function that will allow my users to select from...
by: HowHow | last post by:
I need to sort the "DateOfBirth" by the day (dd) regarless of month (mm)and year (yyyy). I have a query called q_DC_Client, in criteria, I am using this code below: Like "*" & "/" & !! & "/" & "*" I have a form called F_DC_Birthday with combo box called ComboBdayMonth. The combo takes information from T_Month table, where I only have two field, first field is the name of Month (eg, January, February...etc) and second field is 01 to 12. The...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.