| Name: |
MySQL Timestamp Conversion |
| Version: |
V1.0 |
| Size: |
1 KB |
| Written: |
December 15, 2003 |
| Updated: |
December 15, 2003 |
|
Download
|
When I started to work with MySQL, I found that working with dates was a little more complicated than with Microsoft Access. Many versions of this code exist in different places on the internet, unfortunately most of them are poorly written. This code is cleaner and shorter than most of the code I could find at the time.
This is a function to convert a VBScript (ASP) date into a timestamp accepted by a MySQL database server. The function could be converted to one line, but is broken down for explicative purposes.
Code:
<%
Function ConvertSQLTimeStamp(strDateTime)
'Depending on regional settings, VBS may display time with AM/PM, and date as MM-DD-YYYY
'MySQL accepts timestamps in the following format: 'YYYY-MM-DD HH:MM:SS' (military time)
'In reality MySQL will accept timestamps like the following: '1999-1-6 5:4:3' and store
'them as '1999-01-06 05:04:03' appropriately.
'Get the year
ConvertSQLTimeStamp = Year(strDateTime) & "-"
'Get the month
ConvertSQLTimeStamp = ConvertSQLTimeStamp & Month(strDateTime) & "-"
'Get the day
ConvertSQLTimeStamp = ConvertSQLTimeStamp & Day(strDateTime) & " "
'Get the time (HH:MM - military format)
ConvertSQLTimeStamp = ConvertSQLTimeStamp & FormatDateTime(strDateTime, vbShortTime)
'Get and add the second
ConvertSQLTimeStamp = ConvertSQLTimeStamp & ":" & DatePart("s", strDateTime)
End Function
Function ConvertVBSTimeStamp(strDateTime)
'This function is completely unnecessary, however it is here to show how to convert
'MySQL timestamps to a different format. VBS can convert SQL timestamp directly without
'any modifications necessary.
'Format strDateTime using the systems regional settings
ConvertVBSTimeStamp = FormatDateTime(strDateTime)
End Function
%>
|