SQL-Only Date Conversion

0
June 20, 2016

I've done something lazy and now I'm paying the price.

About a year ago I created a MySQL DB that I'm running on a local WebServer (MySQL 3.23.58). I'm using PHPMyAdmin and don't have direct access to the DB itself. I was having a horrible time trying to get Visual Basic to properly convert dates into a format that MySQL liked (my error, totally ignorant of me.) and decided to make a text column with the date format being "MM/DD/YYYY". I figured that I would save everything as text and use CDate commands to make things work programatically.

Here I am a year later and I want to properly store dates so that I can filter results in my query as opposed to using If...Then statements.

Using PHPMyAdmin where I have a simple console, would it be possible to have the database iterate through my rows, take the "MM/DD/YYYY" from "activitydate" (Type: TEXT) and convert it to a proper "YYYY-MM-DD" in "activityrealdate" (Type: Date)? Is this something I can do with an SQL statement or would I need to code a conversion page into my website and do it programmatically?

If this is not possible, would it be possible to, in my SQL query, convert the string to the proper date format?

Thank you very much for helping me with a problem I clearly created myself, due to my own laziness.

Mike

0

answers

Comments See all(0)

Add comment

Related Questions

VB.Net Date formatting (implicit conversion?)

As a VB.6.0 Developer I am fullly aware of the issues surrounding date formatting and being in Australia use the date format "dd-mmm-yyyy hh:nn ampm" with ALL of my dates to ensure the impli Read More

Views

25

Votes

0

Answers

10

August 16, 2011

Challenge with combining week numbers and a calendar year to a calendar date in oracle

I am looking for some suggestions on developing a query that calculates sales activity on a week by week basis historically. I have been writing daily, monthly, and annual sales reports for a long tim Read More

Views

28

Votes

0

Answers

5

August 16, 2011

Date Parameters defaulting to datetimeoffset type in SSRS 2008

In SQL Server Reporting 2008, if a user manually types a date of 11/24 in a datetime parameter box, it will automatically default that date to the new datetimeoffset type in SQL 2008.  This is pr Read More

Views

23

Votes

0

Answers

4

August 17, 2011

Windows Date to UTC (Unix date) Conversion

This worked (for me) when I needed to convert a Windows date field to UTC. Note that this is only for dates. Assumes the UTCdate field has been added. Check the results before deleting the windowsd Read More

Views

15

Votes

0

Answers

0

June 19, 2016

Data Conversion & Data Import in Access 2007

I'm not really sure how to tackle this. I have a table listed bellow called Prescription and I would like to import to this table all the data from two other tables: ClientMedication_Prn_Exct ract Read More

Views

22

Votes

0

Answers

5

August 16, 2011

Reporting Services UTC Date conversion

I have an sql 2005 database with several fields that need to be timezone agnostic.  Using nHibernate interceptors we have fixed the application side and everything is happy.  The problem is Read More

Views

15

Votes

0

Answers

4

August 17, 2011

SQL Server - Change SQL Server dateformat?

Hi, By default sql server will accept date format of mdy.is it possible to change the format to dmy. SET DATEFORMAT dmy GO INSERT tDateOrder (d) VALUES ('31/1/04') GO INSERT tDateOrder (d) VALUE Read More

Views

15

Votes

0

Answers

15

June 19, 2016

Newby help editing MYSQL Database

I have a mysql database file I need to edit. It's for a Zen Cart database. The original file, before I altered it, is listed as the First Code in the included code block. I ran am additional &#160 Read More

Views

349

Votes

0

Answers

12

August 17, 2011

Do you know how I can prevent the Run time error '3010'  table 'FA_ledger_report' already exists ? Actually the 'FA_ledger_report' is a Union query.

Do you know how I can prevent the Run time error '3010'  table 'FA_ledger_report' already exists FA_ledger_report is actually the following Union query: SELECT  COSTCENTER AS Read More

Views

25

Votes

0

Answers

5

August 16, 2011

ADODB.Connection?

<b>I am getting an error that says:</b> "Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Driver Manager] Data source name not found and no default Read More

Views

59

Votes

0

Answers

10

August 16, 2011

INSERT INTO a table with a UNION SELECT containing varying column numbers

Here is a shortened version of a SQL I am trying to perfect. I am in MSAccess 2003 and testing this in the query objects section tbl_questionnaire is an existing linked SQL Server table with no rows. Read More

Views

13

Votes

0

Answers

6

August 16, 2011

Using CSV File as Datafeed

We have a product datafeed of a retailer that he supplied to us in CSV format. Now I have to use this CSV file to import all the data into our database in our own date model. In practice this means th Read More

Views

22

Votes

0

Answers

3

August 17, 2011

How to prevent getting a Runtime Error '-2147467259   Not Enough Disk Space when executing rs.Open "SELECT tblCostCenter.[COSTCENTER] FROM tblCostCenter GROUP BY tblCostCenter.[COSTCENTER];", c ?

Public Function prtFaLedgerExcel()     Dim rs As ADODB.Recordset     Dim cn As ADODB.Connection     Dim fname As String     Dim sql As String     Dim Read More

Views

20

Votes

0

Answers

9

August 16, 2011

For Experts: Working around the missing (cast)

Hello all, I had intended to invent more of these "expert hunt" questions, but it's only issue n°2. {Previous issue: http:/Q_21204203.html } *** Future readers: find a summary of th Read More

Views

27

Votes

0

Answers

18

August 16, 2011

After writing data in to an excel sheet from active directory, how do I convert a particular column to a string?

The following retrieves all users within Active directory and writes them in to an excel sheet and saves the sheet to a network drive.  This works all ok. However before saving the sheet to a net Read More

Views

16

Votes

0

Answers

1

August 16, 2011

This is the very first question

Hello guys! This is the first post in this site Read More

Views

2k

Votes

4

Answers

2

January 08, 2016

report values not showing up.

I have a crystal report which I pass a DataSet to using VB.NET. The report was working fine, but then I make some changes to the import query and now nothing shows up in the crystal report. Crystal do Read More

Views

1k

Votes

0

Answers

12

September 06, 2005

Event log doesn't overwrite as needed in SP4

Hello All, After upgrading from SP3 to SP4 on several Windows 2000 PRO machines everything looked fine, but from time to time applications can't write to the event log. When I try to view the appl Read More

Views

1k

Votes

0

Answers

9

May 11, 2003

"Failed to self-register XYZ.dll"

Hi there, I wrote a OLE-automation-server DLL in VB4.0. I use Installshield Express to install it as part of my program on the target computers. Now on some computers I get the message "Failed to Read More

Views

2k

Votes

0

Answers

2

November 08, 1998

Please help understand these notes on image processing

Can someone please help me understand these lecture notes... On the right of the page; What does "normalised by one notion of the area of a pixel" mean? On the first formula for A, Is that a Read More

Views

1k

Votes

0

Answers

3

February 05, 2009