By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,521 Members | 1,196 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,521 IT Pros & Developers. It's quick & easy.

Usage of CONVERT function in where clause (in SQLSERVER)

P: 1
In the following query D_CNT_LINE_EXPIRE comparison with '2008-09-19' is failing altough value is there in this field. D_CNT_LINE_EXPIRE is a timestamp field in SQLSERVER table CCG03.
I doubt whether we can use CONVERT function in 'where' clause (see query 1)or 'Update set' clause' (see query 2)????????????????
let me know if need more details.
Please help folks.
query 1:
------------
UPDATE CCG03
SET C_CNT_LN_UM = 'AB'
WHERE I_CONTRACT = 12568
AND L_CNT_LINE = 17
AND CONVERT(CHAR(10),D_CNT_LINE_EXPIRE,101)
= '2008-09-19'
query 2:
------------
UPDATE CCG03
SET CONVERT(CHAR(10),D_CNT_LINE_EXPIRE,101)
= '2009-09-19'
WHERE I_CONTRACT = 12568
AND L_CNT_LINE = 17
Jan 31 '08 #1
Share this Question
Share on Google+
1 Reply


deepuv04
Expert 100+
P: 227
In the following query D_CNT_LINE_EXPIRE comparison with '2008-09-19' is failing altough value is there in this field. D_CNT_LINE_EXPIRE is a timestamp field in SQLSERVER table CCG03.
I doubt whether we can use CONVERT function in 'where' clause (see query 1)or 'Update set' clause' (see query 2)????????????????
let me know if need more details.
Please help folks.
query 1:
------------
UPDATE CCG03
SET C_CNT_LN_UM = 'AB'
WHERE I_CONTRACT = 12568
AND L_CNT_LINE = 17
AND CONVERT(CHAR(10),D_CNT_LINE_EXPIRE,101)
= '2008-09-19'
query 2:
------------
UPDATE CCG03
SET CONVERT(CHAR(10),D_CNT_LINE_EXPIRE,101)
= '2009-09-19'
WHERE I_CONTRACT = 12568
AND L_CNT_LINE = 17
hi,

CONVERT(CHAR(10),D_CNT_LINE_EXPIRE,101) takes the date format as '09/09/2008' and you are comparing with '2008-09-19'
and failed

to compare both the dates use the following query

compare the date difference between two dates as
UPDATE CCG03
SET C_CNT_LN_UM = 'AB'
WHERE I_CONTRACT = 12568
AND L_CNT_LINE = 17
AND datediff(day,D_CNT_LINE_EXPIRE,'2008-09-19') = 0

thanks
Jan 31 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.