newbie here: format conversion help needed

[oralover2006]

hi all respected members,

I am new to PHP & MySQL, created working environment on Windows machine with XAMPP.

my question is I want to write an SQL in which I want to include a string conversion for

Feb-2026 -> 02-2026 

I wrote below example after getting help (googling) but it return null in phpMyAdmin console 

select STR_TO_DATE('Mar-2026', %m-%Y) from mytable; 

and it returns null ... 

what's wrong here in my SQL? I am much curious how to resolve it?

please note: i knew this is not a FIELD from table instead it is just a hard coded value, posted it just to show what value table column contain.

Edited January 21 by oralover2006

[Barand]

 

There are a couple of errors in your SQL statement...

• The specified format should be in quotes (it's a string)
• The format code for a short month name id "%b" (%m is month number)

Try

select STR_TO_DATE('Mar-2026', '%b-%Y');      -- > 2026-03-00

Note the function returns a date. To output your required "03-2026" you need to use DATE_FORMAT() on the returned date

select DATE_FORMAT( STR_TO_DATE('Mar-2026', '%b-%Y'), '%m-%Y');

                                               |         |
                                             input      output 
                                             format     format

Easiest is always to store dates as DATE TYPE ie 2026-03-01 instead of Mar-2026.

[oralover2006]

21 hours ago, Barand said:

 

There are a couple of errors in your SQL statement...

• The specified format should be in quotes (it's a string)
• The format code for a short month name id "%b" (%m is month number)

Try

select STR_TO_DATE('Mar-2026', '%b-%Y');      -- > 2026-03-00

Note the function returns a date. To output your required "03-2026" you need to use DATE_FORMAT() on the returned date

select DATE_FORMAT( STR_TO_DATE('Mar-2026', '%b-%Y'), '%m-%Y');

                                               |         |
                                             input      output 
                                             format     format

Easiest is always to store dates as DATE TYPE ie 2026-03-01 instead of Mar-2026.

thank you @Barand for your help, don't know why but your SQL return NULL value.

I tried further, below is my solution which returns exact value what I needed. if it is not appropriate please help to correct it because only end result is not complete solution may be there are several things in my SQL statement which require correction(s).

select
SUBSTRING(
CAST(
DATE_FORMAT(
STR_TO_DATE('Mar-2025-01', '%b-%Y-%d'),
    '%m-%Y-%d') as CHAR),
    1,7)
AS result;

thank you again.

Edited January 22 by oralover2006

[Barand]

My query worked fine for me...

mysql> select DATE_FORMAT( STR_TO_DATE('Mar-2026', '%b-%Y'), '%m-%Y');
+---------------------------------------------------------+
| DATE_FORMAT( STR_TO_DATE('Mar-2026', '%b-%Y'), '%m-%Y') |
+---------------------------------------------------------+
| 03-2026                                                 |
+---------------------------------------------------------+

To better simulate what you are doing...

CREATE TEST TABLE:

mysql> CREATE TEMPORARY TABLE test1(datestr VARCHAR(8));

mysql> INSERT INTO test1 VALUES('Jan-2026'), ('Feb-2026'),('Mar-2026');

QUERY AND REFORMAT:

mysql> SELECT datestr
    ->      , DATE_FORMAT( STR_TO_DATE(datestr, '%b-%Y'), '%m-%Y') as formatted
    -> FROM test1;
+----------+-----------+
| datestr  | formatted |
+----------+-----------+
| Jan-2026 | 01-2026   |
| Feb-2026 | 02-2026   |
| Mar-2026 | 03-2026   |
+----------+-----------+

 

9 hours ago, oralover2006 said:

because only end result is not complete solution

Then what are you really trying to achieve?

[gizmola]

On 1/21/2026 at 3:20 AM, oralover2006 said:

hi all respected members,

I am new to PHP & MySQL, created working environment on Windows machine with XAMPP.

 

Welcome to the world of PHP development.  

It is unfortunately the case that for some reason, most people new to php development, particularly using Windows, still default to the installation of Xampp.  

My first comment is that Wamp/Mamp/xampp etc. are all projects that haven't been used by professional developers in a long time.  This change in approach to local development was hastened by the commodity support of virtualization built into intel and amd cpu's.  Every PC that is more powerful than a potato supports virtual environments.  Developers first started developing with or within virtualized environments using software like Vmware workstation and Virtualbox in the mid to late 2000's.  It's important to understand virtualization, and how it is the basis for  cloud and vps servers.  This was an evolution that began close to 20 years ago at this point.  Many professional developers were developing PHP apps within virtualized linux systems back in 2007-2008.  The orchestrator "Vagrant" was released in 2010 with a 1.0 version in 2012 and was for a time, the favored approach for many developers and development teams as a way to harness the virtualbox and VMware hypervisors to build simplify and orchestrate virtual linux based environments.  I might add that Windows now includes it's own microsoft provided hypervisor - hyper-v, so the option to run fully virtualized servers within windows is still an option for development on a windows workstation.

Xampp is an antiquated approach to php development with many drawbacks including:

• It's based on LAMP (Linux, Apache, MySQL, PHP) in a particular configuration that few websites use anymore for various reasons
  • The once popular apache/mod_php integration has been superceded by the use of the php_fpm server component
  • other http servers/proxies are often used, with nginx being a common choice. 
• It's a wrapper for windows native ports of the components.  PHP apps aren't deployed to windows servers -- they are deployed to Linux servers 99.9% of the time
  • It makes no sense to develop your application in an environment you won't be deploying to, and there are also OS specific differences which means that some php code that works in a linux environment won't work under windows and vice-versa
• Developers are often unaware that they have installed local server components which are running (unless they proactively start/stop them) all the time. 
• These projects (xampp, wampserver, easyphp, mamp..... and many others) have been dying off due to lack of interest, and have to be updated frequently, requiring re-installation.  It's old, and ugly, and people have been coming up against problems with them for over a decade, or finding that their favorite environment package has fallen behind or been abandoned entirely.  Windows based developers should prefer the use of windows application installation through package managers (Chocolatey, Scoop, Winget etc.) just as Mac users prefer homebrew, and linux users utilize distro specific package managers (apt, yum/dnf, flatpak, etc). Again these windows package tools utilize a cli environment, rather than the old "find a website, download a .exe setup, run it and hope this new program does what you need it to do."
• The setup of tools and disconnect in how apps can or should be deployed from development to product leads to confusion
• The configuration files that are used to configure apache and php is hidden from the user
• competent professional php development involves learning the basics of cli tools, use of git, and the composer cli tool. 
  • Xampp is intended to "simplify" by hiding the cli, and thus discourages and sends the message to a new developer that they don't need to use the cli, which is counterproductive

Windows users have a variety of far better options with the main 2 being:

• use WSL to install a linux distro integration, and work within WSL
  • fast, performant and makes the development experience closer to what osx and linux developers do
• use Docker based environments
  • Provides the ultimate degree of project by project flexibility and can provide a local development environment that includes an orchestrated set of server components that would be extremely difficult to set up locally using any alternative approach.
  • Easily switch between php versions/databases/http servers on a project by project basis
  • Is supported by multiple projects that simplify the use of docker (DDEV, Lando, Docksal -- others)
    • I advise most people to install DDEV now

The only thing Xampp is going for windows based developers now, is teaching old mostly obsolete development.  If a tutorial or article starts with the use of Xampp, it almost always provides bad advice and techniques and code samples that no experienced developer would use.  There are numerous resources that can be used to study and learn modern PHP development, and anyone running windows can find numerous existing resources (tutorials, guides, articles, free and paid online courses) that will show you how to use wsl or docker/ddev.  This will also lead you to understand and become proficient in the use of component libraries and frameworks.  These things tend to come together once you start to understand the use and purpose of composer, which should be the first step (after making a project directory) you should be using (unless a tool like DDEV or the symfony cli tool has run done that setup for you).