CODEWITHSUNDEEP
sql-serversql-server-2008datetimeview
ayhan engin
ayhan engin

Reputation: 1

convert datetime is not running to view,

i tried to this query. but its not running. how can i do?

SELECT dbo.isemri_data.sipnum, 
       dbo.kayitlar.id, 
       dbo.kayitlar.makina_id, 
       dbo.kayitlar.personel, 
       dbo.kayitlar.isemrino, 
       dbo.kayitlar.tarih, 
       dbo.kayitlar.gercek_hiz, 
       dbo.kayitlar.durus_kod, 
       dbo.kayitlar.miktar, 
       dbo.kayitlar.fire 
FROM   dbo.isemri_data 
       LEFT OUTER JOIN dbo.kayitlar 
                    ON dbo.isemri_data.isemrino = dbo.kayitlar.isemrino 
WHERE  CONVERT(VARCHAR(10), dbo.kayitlar.tarih, 104) BETWEEN 
              CONVERT(VARCHAR(10), '12.08.2015', 104) AND 
              CONVERT(VARCHAR(10), '19.08.2015', 104) 
       AND CONVERT(VARCHAR(3), dbo.kayitlar.makina_id) = 'M1' 
       AND dbo.kayitlar.isemrino LIKE '%'

if delete 

CONVERT(varchar(10),dbo.KAYITLAR.TARIH,104) BETWEEN convert(varchar(10),'12.08.2015',104) and convert(varchar(10),'19.08.2015',104)

query is running.

or if run to only main table allready running my query.

i cant find what is the problem.

Upvotes: 0

Views: 87

Answers (2)

ayhan engin
ayhan engin

Reputation: 1

i found answer. if i use

dbo.kayitlar.tarih BETWEEN CONVERT(DATETIME, '12.08.2015', 104) AND CONVERT(DATETIME, '19.08.2015', 104)

running query.

thanx for your help

Upvotes: 0

M.Ali
M.Ali

Reputation: 69564

Since you have mentioned the data type is Datetime , DO NOT convert it to anything else in there where clause, do it in select if you want to. 

SELECT dbo.isemri_data.sipnum, 
       dbo.kayitlar.id, 
       dbo.kayitlar.makina_id, 
       dbo.kayitlar.personel, 
       dbo.kayitlar.isemrino, 
       dbo.kayitlar.tarih, 
       dbo.kayitlar.gercek_hiz, 
       dbo.kayitlar.durus_kod, 
       dbo.kayitlar.miktar, 
       dbo.kayitlar.fire 
FROM   dbo.isemri_data 
       LEFT OUTER JOIN dbo.kayitlar 
                    ON dbo.isemri_data.isemrino = dbo.kayitlar.isemrino 
       AND  dbo.kayitlar.tarih  >= '20150812'  -- use ANSI date YYYYMMDD
       AND  dbo.kayitlar.tarih <=  '20150819'  -- use ANSI date YYYYMMDD
       AND  CONVERT(VARCHAR(3), dbo.kayitlar.makina_id) = 'M1' 
       AND  dbo.kayitlar.isemrino LIKE '%'

Converting your datetime to a string means sql server treats them values as strings and not a date/datetime values hence you will get unexpected results Also sql server will not be able to make use of any indexes defined on that column if there are any.

Keep date/datetime values as it is and change the format of date in presentation layer for more easy to eyes date/datetime format.

Upvotes: 1

Related Questions