CODEWITHSUNDEEP
sqlsql-serverdatetime
Steam
Steam

Reputation: 9856

Convert datetime to specific format?

I want to convert a datetime to a specific format. The conversion should result in a variable of type datetime and not char or varchar. How do I do this in SQL server 2000, 2005 and 2008 ?

select CONVERT(varchar(30),getdate(),120)

I tried this, but it gives me a string. I want a datetime without the milli-seconds. SS 2012 has an option for this, but not previous versions.

http://blog.sqlauthority.com/2012/11/21/sql-server-display-datetime-in-specific-format-sql-in-sixty-seconds-033-video/

Upvotes: 2

Views: 1579

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You can remove the milliseconds from the datetime like this:

DATEADD(ms, -DATEPART(ms, date), date) > '2013-11-18 03:21:52'

Also check SQL Server Date Formats

or may be try like this to remove the millisecond part:-

declare @str datetime
set @str = '2013-11-18 17:24:05.784'
select convert(datetime, convert(char(19), @str, 126))

Upvotes: 1

BWS
BWS

Reputation: 3836

You can't have it both ways ... a variable of type "datetime" is defined as: 

Defines a date that is combined with a time of day 
with fractional seconds that is based on a 24-hour clock

You can CONVERT and DISPLAY in whatever format you choose, but the datetime data type will always have the milliseconds, even if you set them to zero.

Upvotes: 2

Related Questions