CODEWITHSUNDEEP
sql-serversql-server-2008datetimetime
Marina
Marina

Reputation: 3322

What is the difference between these ways to return the time for a datetime column in SQL Server?

Right now, I know of 3 ways to return the time:

  1. CONVERT(varchar, Date, 8)

  2. CONVERT(varchar, Date, 108)

  3. CONVERT(time, Date)

What is the difference between #1 and #2, since they return the same answer?

Why does #3 return something that looks different--is it still equivalent to #1/#2's output?

Are there more ways?

Thanks!

Upvotes: 1

Views: 66

Answers (2)

Andomar
Andomar

Reputation: 238246

Have a look at the MSDN page for convert. The format 8 is hh:mi:ss. If you add 100 to a format, it uses a four-digit year instead of a two digit year. Since format 8 is a time format, 8 and 108 have the same output.

The third option converts a datetime to a time. A time is not a string and has no format. It's up to the client how it will be displayed, typically using your local computer's Regional and Language Settings.

Upvotes: 6

aF.
aF.

Reputation: 66727

Points 1. and 2. are equal. Point 3 returns in other datatype.

Points 1 and 2 are equal but point 3 is different from them.

You can check different formats here.

Upvotes: 1

Related Questions