blob150
asked on
Checking for NULL values before converting MySQL Dates
another user helped me create a functions to convert mm/dd/yyyy date formats to yyyy-mm-dd, but the function throws an error if the original date record is null. Can someone help me with a few lines of code to avoid the null value error? I need the field to show empty if no date is present. The function I am using is:
<%
function DateToMySQL(strDate)
dim tmp
tmp = split(strDate, "/")
tmp(0) = formatNumber(tmp(0))
tmp(1) = formatNumber(tmp(1))
DateToMySQL = "20" & tmp(2) & "-" & tmp(0) & "-" & tmp(1)
end function
function formatNumber(num)
if int(num) < 10 then
formatNumber = "0" & num
else
formatNumber = num
end if
end function
response.write DateToMySQL("4/2/06")
%>
Please advise. Thanks.
<%
function DateToMySQL(strDate)
dim tmp
tmp = split(strDate, "/")
tmp(0) = formatNumber(tmp(0))
tmp(1) = formatNumber(tmp(1))
DateToMySQL = "20" & tmp(2) & "-" & tmp(0) & "-" & tmp(1)
end function
function formatNumber(num)
if int(num) < 10 then
formatNumber = "0" & num
else
formatNumber = num
end if
end function
response.write DateToMySQL("4/2/06")
%>
Please advise. Thanks.
blob150,
You can test for empty or null in asp (vbscript) by using something like the code below:
If str <> "" then
' It is not empty so do this
End if
In this case the 2 double quotes will work to test for empty or null but I wasn't real sure in the code you provided where the error was. I think this is what you want though ...
function DateToMySQL(strDate)
If strDate <> "" then
dim tmp
tmp = split(strDate, "/")
tmp(0) = formatNumber(tmp(0))
tmp(1) = formatNumber(tmp(1))
DateToMySQL = "20" & tmp(2) & "-" & tmp(0) & "-" & tmp(1)
End if
end function
Let me know if you need more information or have a question.
b0lsc0tt
You can test for empty or null in asp (vbscript) by using something like the code below:
If str <> "" then
' It is not empty so do this
End if
In this case the 2 double quotes will work to test for empty or null but I wasn't real sure in the code you provided where the error was. I think this is what you want though ...
function DateToMySQL(strDate)
If strDate <> "" then
dim tmp
tmp = split(strDate, "/")
tmp(0) = formatNumber(tmp(0))
tmp(1) = formatNumber(tmp(1))
DateToMySQL = "20" & tmp(2) & "-" & tmp(0) & "-" & tmp(1)
End if
end function
Let me know if you need more information or have a question.
b0lsc0tt
Whoops: forgot exit:
function DateToMySQL(strDate)
if not isdate(strDate) then
DateToMySQL = "Not valid date"
exit function
end if
dim tmp
tmp = split(strDate, "/")
tmp(0) = formatNumber(tmp(0))
tmp(1) = formatNumber(tmp(1))
DateToMySQL = "20" & tmp(2) & "-" & tmp(0) & "-" & tmp(1)
end function
That is just one way to do it...
function DateToMySQL(strDate)
if not isdate(strDate) then
DateToMySQL = "Not valid date"
exit function
end if
dim tmp
tmp = split(strDate, "/")
tmp(0) = formatNumber(tmp(0))
tmp(1) = formatNumber(tmp(1))
DateToMySQL = "20" & tmp(2) & "-" & tmp(0) & "-" & tmp(1)
end function
That is just one way to do it...
@alorentz - Sorry should've refreshed. I like your suggestion better!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@b0lsc0tt - no problem...
ASKER
Thanks for all the help alorentz & b0lscott.
ASKER
Looks like I need to do the reverse. I can get the function to display the dates as yyyy-mm-dd when the page loads, but now I need the dates to display as mm/dd/yy and convert to yyyy-mm-dd only after the form is submitted. How can I do that?
Take the time to read on see what the function I gave does, and figure it out....very simple. No offense, but you're relying on people giving you the answer too much, rather than learning. The function I gave is very basic.
Is this the same thing you asked in this new question (https://www.experts-exchange.com/questions/21878092/Coverting-Date-Format-mm-dd-yy-to-yyyy-mm-dd-To-Insert-into-MySQL.html? If so, then it is probably the best place for Alorentz or myself to answer. Thanks for bringing our attention to it.
If the question in the link I provided is different then it would be best to ask this in a new question. I think posting a link here to draw our attention to it would be fine and I doubt that Alorentz would mind. However there are a number of reasons it is good to give each issue its own question. Let me know if you have any questions about this.
To add to what Alorentz just wrote his function uses built-in functions (day, month, year) that can be very easily modified and are versatile.
If the question in the link I provided is different then it would be best to ask this in a new question. I think posting a link here to draw our attention to it would be fine and I doubt that Alorentz would mind. However there are a number of reasons it is good to give each issue its own question. Let me know if you have any questions about this.
To add to what Alorentz just wrote his function uses built-in functions (day, month, year) that can be very easily modified and are versatile.
ASKER
Thanks for the help guys. It is the same question I posted in the other location and I will avoid duplicating posts in the future. I am new to this game and I have been able to figure most stuff out, but for some reason these date issues are driving me nuts.
As done in the function...break the date into it's individual pieces (month, day, year)...and then you can put back to gether any way you want.
Date functions: http://www.w3schools.com/vbscript/vbscript_ref_functions.asp
Date functions: http://www.w3schools.com/vbscript/vbscript_ref_functions.asp
if not isdate(strDate) then
DateToMySQL = "Not valid date"
end if
dim tmp
tmp = split(strDate, "/")
tmp(0) = formatNumber(tmp(0))
tmp(1) = formatNumber(tmp(1))
DateToMySQL = "20" & tmp(2) & "-" & tmp(0) & "-" & tmp(1)
end function