Date Format

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Joel
    #1

    Date Format


    I recently converted data and discovered that when the date wasn't formatted
    properly '01/01/001' (I think) that I was unable to find the record using
    Sql i.e 'Select * from table where order_#=100' . However, using utilities
    (Navicat) I was able to find the record. Any reason?

    PS In a way I was happy because it helped me determine how I got the bad
    data!



    Tags: None
    • Aggro
      #2
      Re: Date Format

      Joel wrote:[color=blue]
      > I recently converted data and discovered that when the date wasn't formatted
      > properly[/color]

      Are you using the proper format yyyy-mm-dd (for example 2005-11-02)?
      Save dates always in this format to database. Use date functions or
      functions at the application side, if you need to display it in another
      format.

        Comment

        • Joel
          #3
          Re: Date Format


          I realize I did not use the proper date function. The problem is:

          1. MySql accepted the dates
          2. I was unable to find the records with bad dates using Sql. However, using
          Navicat utility I was able to find the records!


          "Aggro" <spammerdream@y ahoo.com> wrote in message
          news:8L9af.415$ 1i5.124@read3.i net.fi...[color=blue]
          > Joel wrote:[color=green]
          > > I recently converted data and discovered that when the date wasn't[/color][/color]
          formatted[color=blue][color=green]
          > > properly[/color]
          >
          > Are you using the proper format yyyy-mm-dd (for example 2005-11-02)?
          > Save dates always in this format to database. Use date functions or
          > functions at the application side, if you need to display it in another
          > format.[/color]


            Comment

            • Aggro
              #4
              Re: Date Format

              Joel wrote:[color=blue]
              > I realize I did not use the proper date function. The problem is:
              >
              > 1. MySql accepted the dates[/color]

              Yes, you should validate the dates before inserting them to database if
              you need the database to contain valid data.
              [color=blue]
              > 2. I was unable to find the records with bad dates using Sql. However, using
              > Navicat utility I was able to find the records![/color]

              Usually valid dates are quite near the current year. So you should be
              able to find invalid dates by searching for dates that are either far in
              the past or far in the future or null.

              select * from tablename where date < '1980-01-01' or date > '2020-01-01'
              or date is null;

              Adjust the time frame according to what you would expect the dates to be
              if they are correct. The above example should work if you are using
              columns that have type date or datetime.

                Comment

                Previous template Next
                Working...