[ciapug] MS SQL timestamp to MySQL

[Chris Hettinger]

I am asking for the date of birth on the form, with the format of =
mm/dd/yyyy.

When I process the values of my form input, I will have to convert that =
date to yyyy-mm-dd hh:mm:ss to store it in my MySQL datetime field. Part =
of processing my form data vs my SQL result set is to compare the DoB to =
verify identity, so I am concerned about the mismatch in format. In =
addition the MS SQL table I am going to to need to store it eventually =
is in the format yyyy-mm-dd hh:mm:ss.sss. So when I do my later inserts =
I will need to do this conversion.

I was recommended the CONVERT function so I can do some of this in =
query... I can do it outside the query if I need to, just asking for an =
example of how to use CONVERT, cause I didn't find a good syntax example =
dealing with date. Most doc use it with financal data examples... N E =
Way... I will take a look at that link, maybe I clarified my question?

So yes I am using datetime in all cases for that same reason, in case I =
need it.


-----Original Message-----
From: David Champion [mailto:dave@visionary.com]
Sent: Thursday, October 02, 2003 4:02 PM
To: ciapug@cialug.org
Subject: Re: [ciapug] MS SQL timestamp to MySQL


Is your mm/dd/yyyy date in mysql a character field, or date? IMHO you=20
should always store dates in a date or datetime field. I usually use=20
datetime, that way if I need the time I can use it, otherwise I just=20
ignore the time and use the date.

I think this is what you need to use:

http://www.mysql.com/doc/en/Cast_Functions.html

-dc

Chris Hettinger wrote:

> I have a MS SQL datetime field with the format [yyyy-mm-dd =
hh:mm:ss.mmm] (milliseconds) that I need to compare to a date entered =
into my form as [mm/dd/yyyy]
>=20
> I was going to use my inputted date string ex. 06/30/1977 and convert =
that into a MySQL time stamp 1977-06-03 00:00:00. Well that is when I =
noticed the ms SQL format for the date was different.
>=20
> I asked a ms sql guy here and he recommended to me the CONVERT funtion =
that I can include in my SQL select statement to do the datetime =
conversion, to the format I want the date in, while building the records =
set that I will need to compare against. So I researched that but I =
haven't a good example of the syntax for the actual SQL statement.
>=20
> Would anyone have a suggestion or could lend an example of how to use =
the CONVERT to do this?
>=20
>=20
> -Chris Hettinger, Web Specialist
> -IFMC/ENCOMPASS
> -www.encompas.com
> -(515) 279-8730
>=20
>=20
>=20
> CONFIDENTIALITY NOTICE:  This communication, including any attachment, =
may contain confidential information and is intended only for the =
individual or entity to whom it is addressed.  Any review, =
dissemination, or copying of this communication by anyone other than the =
intended recipient is strictly prohibited.  If you are not the intended =
recipient, please contact the sender by reply email, delete and destroy =
all copies of the original message.'
>=20
> _______________________________________________
> ciapug mailing list
> ciapug@cialug.org
> http://cialug.org/mailman/listinfo/ciapug
>=20


_______________________________________________
ciapug mailing list
ciapug@cialug.org
http://cialug.org/mailman/listinfo/ciapug


CONFIDENTIALITY NOTICE:  This communication, including any attachment, =
may contain confidential information and is intended only for the =
individual or entity to whom it is addressed.  Any review, =
dissemination, or copying of this communication by anyone other than the =
intended recipient is strictly prohibited.  If you are not the intended =
recipient, please contact the sender by reply email, delete and destroy =
all copies of the original message.'