SQLTeam.com | Weblogs | Forums

Help for select

when selecting the data, I want the documentdate(belgetarihi) date format to be just like the due date (vadetarihi)how convert please help

SELECT ICE_ODEMEBASLIK.* 
 ,ORT_DOSYALAR_kayitdamga AS DSY_kayitdamga
 ,HP.ID           HP_ID
 ,HP.planadi      HP_planadi
 ,KP.ID           KP_ID
 ,KP.planadi      KP_planadi
 ,ICE_CARIBANKA.ID      CBANKA_ID
 ,ICE_CARIBANKA.kod     CBANKA_kod
 ,ICE_CARIBANKA.hesapnr CBANKA_hesapnr
 ,ICE_CARIBANKA.subenr  CBANKA_subenr
 ,ICE_CARIBANKA.subeadi CBANKA_subeadi
 ,ICE_ODEMEBASLIK.belgetarihi
FROM ICE_ODEMEBASLIK WITH (NOLOCK)  
LEFT JOIN ORT_PLAN AS HP WITH (NOLOCK)   ON ICE_ODEMEBASLIK.plankod      = HP.plankod
LEFT JOIN ORT_PLAN AS KP WITH (NOLOCK)   ON ICE_ODEMEBASLIK.karsiplankod = KP.plankod
LEFT JOIN (SELECT DISTINCT ORT_DOSYALAR.kayitdamga AS ORT_DOSYALAR_kayitdamga  FROM ORT_DOSYALAR  WITH (NOLOCK)   WHERE ORT_DOSYALAR.tablo = 6) AS DOSYA ON ICE_ODEMEBASLIK.kayitdamga=ORT_DOSYALAR_kayitdamga
LEFT JOIN ICE_CARIBANKA WITH (NOLOCK)   ON ICE_CARIBANKA.kayitdamga = ICE_ODEMEBASLIK.UQ_caribanka
WHERE 1 = 1 
 and ICE_ODEMEBASLIK.IOdurum   = -1
 and ICE_ODEMEBASLIK.odemetipi = 1
 and ICE_ODEMEBASLIK.pin      = 0
 and ICE_ODEMEBASLIK.belgetarihi >= CONVERT(DATETIME,'01.01.2022',103)
 and ICE_ODEMEBASLIK.belgetarihi <  CONVERT(DATETIME,'01.01.2023',103)

ORDER BY CONVERT(DATE,ICE_ODEMEBASLIK.belgetarihi,103),ICE_ODEMEBASLIK.IDfisbaslik,ICE_ODEMEBASLIK.vadetarihi

looks like the time part is all zeros in the "vade"

cast("bel" as date) will do the trick

-- another idea is

find out the format of the "vade" column
then use that convert format on "bel" column

convert("vade column format","bel column)

-- another idea is
convert both the "bel" and "vade" to the same format
convert("bel",222) = convert("vade",222)