Date Format Change MSSQL to MySQL

I moved a number of my databases from Microsoft MSSQL, which I’d used for probably twenty years now with some of my sites, over to MySQL. I’d already been using MySQL on those servers for my WordPress blogs. My primary motivator for the migration was that having both MSSQL and MySQL running concurrently on the same server was causing slowdowns. I figured that having all my data in MySQL would ease server load.

However, one problem which resulted from this migration involved the default date format involved in both systems. MSSQL had been set to show dates in the format 11-20-2019. MySQL had been set to show dates in the format 2019-11-20. My ASP Classic Now() function was getting dates in the 11-20-2019 format which had worked great for MSSQL but was now throwing date errors when accessing the data in MySQL.

For various reasons, I found the best solution in my situation was to add a quick script to my ASP Classic page when I fetched a date, in order to rearrange it into MySQL format before using the date for a query.

Here’s the scripts.

If the date field I was checking against only had the year month and date I would do this:

NowDate = Now()
dd = Right("00" & Day(NowDate), 2)
mm = Right("00" & Month(NowDate), 2)
yy = Year(NowDate)
NowDateForSQL = yy & "-" & mm & "-" & dd 

If the date field had the full time involved as well, the script looked like this:

FeatDate = FeatSite("artdate")
dd = Right("00" & Day(FeatDate), 2)
mm = Right("00" & Month(FeatDate), 2)
yy = Year(FeatDate)
hh = Right("00" & Hour(FeatDate), 2)
nn = Right("00" & Minute(FeatDate), 2)
ss = Second(FeatDate)
FeatDateForSQL = yy & "-" & mm & "-" & dd & " " & hh & ":" & nn & ":" & ss

This date format update was the main change I needed to make to my ASP classic code in order to get it to work with the data now in MySQL. The other parts of my scripts all handled the database change without even batting an eye.

I found that to be pretty impressive.

Ask with any questions!

I’ve already documented here how the actual data migration went. It went fairly smoothly.

Migrating a Database from Microsoft MSSQL to MySQL