[ciapug] MS SQL timestamp to MySQL

[David Champion]

If at all possible, use <option> lists for entries. I wrote a set of 
fn's to generate & process dates that we use internally. Nothing there 
that's rocket science. Just makes 3 <option> lists for month, day & 
year. It accepts a value to use as the "default" date.

That way there's a lot less chance that a user will enter a bogus date. 
You'll still need validation to make sure they don't select 02/31/2003.

-dc

Chris Hettinger wrote:

> 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 
> should always store dates in a date or datetime field. I usually use 
> datetime, that way if I need the time I can use it, otherwise I just 
> 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]
>>
>>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.
>>
>>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.
>>
>>Would anyone have a suggestion or could lend an example of how to use the CONVERT to do this?