Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – Multiple ways to get Date and Time separately from DateTime’

How to separate date and time from DateTime ? I came across this question many times in multiple blogs.

Given below are different methods to separate date and time from datetime which are compatible with different versions. Make sure you are using correct method in your SQL Server version.

Mehtod 1 :
This method will work under SQL Server 2000 and above.

Declare @Datetime as datetime
Set @Datetime =getdate()
Select @Datetime as [Datetime]
Select Convert(varchar(10),@Datetime,120) as [Date]
Select Convert(varchar(20),@Datetime,114) as [Time]
--OUTPUT

Datetime
———————–
2013-03-18 22:02:02.960

(1 row(s) affected)

Date
———-
2013-03-18

(1 row(s) affected)

Time
——————–
22:02:02:960

(1 row(s) affected)

Mehtod 2 :
This method will work under SQL Server 2008 and above.

Declare @Datetime as datetime
Set @Datetime =getdate()
Select @Datetime as [Datetime]

Select Convert(Date,@Datetime) as [Date]
Select Convert(Time,@Datetime) as [Time]
--OUTPUT

Datetime
———————–
2013-03-18 22:04:24.930

(1 row(s) affected)

Date
———-
2013-03-18

(1 row(s) affected)

Time
—————-
22:04:24.9300000

(1 row(s) affected)

Mehtod 3 :
This method will work in SQL Server 2012 and above.

Declare @Datetime as datetime
Set @Datetime =getdate()
Select @Datetime as [Datetime]

Select Format(@Datetime,'yyyy-mm-dd') as [Date]
Select Format(@Datetime,'hh:mm:ss tt') as [Time]
--OUTPUT

Datetime
———————–
2013-03-18 22:14:23.763

(1 row(s) affected)

Date
———————–
2013-14-18

(1 row(s) affected)

Time
———————–
10:14:23 PM

(1 row(s) affected)

Conclusion :
I have mentioned 3 different methods to separate date and time from datetime but if you have SQL Server 2008 and above, then I would recommend Method 2.The reason behind this recommendation is in Method 2 we just convert datetime into a date type and time type but in other methods we first convert datetime into date type and time type and then give a style as well.

Read Full Post »