Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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.
0
blob150
Asked:
blob150
  • 6
  • 3
  • 3
1 Solution
 
alorentzCommented:
function DateToMySQL(strDate)
   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
0
 
b0lsc0ttCommented:
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
0
 
alorentzCommented:
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...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
b0lsc0ttCommented:
@alorentz - Sorry should've refreshed.  I like your suggestion better!
0
 
alorentzCommented:
Also, be careful what answers you accept...that date formatting solution is ridiculous:

Use this:

function DateToMySQL(strDate)
   if not isdate(strDate) then
      DateToMySQL = "Not valid date"
      exit function
   end if
    dim tmp
   
    yr = year(strDate)
    mo = month(strDate)
    dy = day(strDate)
   
   
    DateToMySQL = yr & "-" & mo & "-" & dy
end function
0
 
alorentzCommented:
@b0lsc0tt - no problem...
0
 
blob150Author Commented:
Thanks for all the help alorentz & b0lscott.
0
 
blob150Author Commented:
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?
0
 
alorentzCommented:
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.
0
 
b0lsc0ttCommented:
Is this the same thing you asked in this new question (http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21878092.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.
0
 
blob150Author Commented:
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.
0
 
alorentzCommented:
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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now