comparing dates with different formats

• EamonSQL

  SSCrazy

  Points: 2307

  More actions

  February 28, 2006 at 7:22 am

  #152788

  Hi,

  I have a date column,Date_A, from table_A in this format 2004-03-12 02:31:03.000

  and another from table_B,Date_B, in this format 04/01/2006

  I wish to joing the two table using the dates but want to disregard the seconds on Date_A.

  select *

  from

  Table_A join Table_B on Date_A = Date_B

  do I need to do a convert on the date with the seconds??

  Any help welcome

  Thanks in advance,

  Eamon

• Antares686

  SSC Guru

  Points: 125444

  More actions

  February 28, 2006 at 7:38 am

  #623683

  Maybe what datatype is Date_A and what type is Date_B. If they are not the same type I suggest doing a cast. I would suggest doing like so personally

   

  select *

  from

  Table_A join Table_B on

  cast(Date_A as datetime) = cast(Date_B as datetime)

• Trystan Hughes

  SSCrazy

  Points: 2109

  More actions

  February 28, 2006 at 7:39 am

  #623684

  Yes, convert the date to the format you want - look on BOL for teh various formats. You may have to use the LEFT function as well to trim the seconds from the date.

  Tryst

• paramind

  SSCrazy

  Points: 2160

  More actions

  February 28, 2006 at 7:51 am

  #623690

  I suggest you cast only one of them, the one with the extended time information. You don't have to care about the format, since internally there's no difference in the storage.

  
  _/_/_/ paramind _/_/_/

• EamonSQL

  SSCrazy

  Points: 2307

  More actions

  February 28, 2006 at 8:35 am

  #623710

  Guys,

  I casted just one of then just to give me dd/mm/yyyy

  I used convert and style 103.

  Thanks,

  Eamon

• AMIT GUPTA-263376

  SSCarpal Tunnel

  Points: 4244

  More actions

  February 28, 2006 at 9:12 am

  #623722

  HI,

  USE THIS QUERY,,

  select *

  from

  Table_A join Table_B on convert(datetime,convert(varchar(10),Date_A,121)) = Date_B

   

  REGARDS

  AMIT