469,156 Members | 2,103 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,156 developers. It's quick & easy.

Usage of CONVERT function in where clause (in SQLSERVER)

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
1 4787
deepuv04
227 Expert 100+
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.

Similar topics

2 posts views Thread by jaYPee | last post: by
2 posts views Thread by Jim.Mueksch | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.