why this query wasn't change the datetime style

Question

select convert(varchar, '2023/07/23',4)

when i execute this i got exactly which date i mentioned above

ok! but is this query get the result as i was thinking let see

select (convert(date, '2023/07/23',4)

no..it does not changed , same date was coming : 2023/07/23

but when i use getdate() it can change why

select Convert(varchar, getdate(),104)

it can change like : 7/27/2023

Answer 1

select convert(varchar, '2023/07/23',4) when i execute this i got exactly which date i mentioned above

You are converting a string to a string. Yeah, that string looks like a date, but the data type is still varchar. The 4 there means nothing in this context.

select (convert(date, '2023/07/23',4) no..it does not changed , same date was coming : 2023/07/23

Here you are converting a string to a date, and you say the interpretation should be according to format code 4 (which I don't recall on the top of my head which it is.) And when I try it, I get the error Conversion failed when converting date and/or time from character string.

select Convert(varchar, getdate(),104) it can change like : 7/27/2023

Now you are converting a value of the type datetime to varchar, and you specify that you want the string be on the form m/dd/yyyy, which apparent is what 104 stands for.

Answer 2

Hi @Giri Prakash

CONVERT syntax: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Only for a date or time data type expression, style can have values like 4 or 104.

--Query 1
select convert(date, '2023/07/23',4)
--Query 2
select Convert(varchar, getdate(),104)

The expression in Query1 is '2023/07/23' which is varchar type.

While the expression in Query2 is getdate() which returns datetime type value.

Please refer to this doc for more details: CAST and CONVERT (Transact-SQL)

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.