CODEWITHSUNDEEP
sql-server
Gopal
Gopal

Reputation: 11972

How to get a time and Date Separately?

Using SQL Server 2005

Table1

Time

12/05/2009 08:30:49
12/05/2009 17:00:00
13/05/2009 21:00:00

...,

Above Table Both values are same column, Here I want to separate column like Date and Time

Expected Output

Date       Time

12/05/2009 08:30:49
12/05/2009 17:00:00
13/05/2009 21:00:00

Upvotes: 2

Views: 25478

Answers (4)

kevinw
kevinw

Reputation: 2178

Just a slight modification to pmarflee's answer, because you appear to be wanting the date as dd/mm/yy rather than mm/dd/yy:

SELECT CONVERT(VARCHAR(8) , GETDATE() , 108) AS [time], CONVERT(VARCHAR(10) , GETDATE() , 103) AS [date]

Upvotes: 1

DVK
DVK

Reputation: 129373

select 
CONVERT(VARCHAR(8), GETDATE(), 108) as [time],
CONVERT(VARCHAR(8), GETDATE(), 111) as [date]

See this reference for all formats

Upvotes: 1

pmarflee
pmarflee

Reputation: 3428

The following query returns the date and time respectively for the current system date/time:

SELECT CONVERT(VARCHAR(8) , GETDATE() , 108) AS [time], CONVERT(VARCHAR(10) , GETDATE() , 101) AS [date]

Upvotes: 3

Andomar
Andomar

Reputation: 238058

You can retrieve separate date and time parts like:

SELECT 
    CONVERT(VARCHAR(10),DateField,101) as DatePart,
    CONVERT(VARCHAR(10),DateField,108) as TimePart
FROM YourTable

For more information, see the CONVERT documentation.

The code snippet above returns the DataPart and TimePart as a varchar. If you're interested in retrieving them as a datetime field, try:

SELECT 
    DATEADD(D, 0, DATEDIFF(D, 0, DateField)) as DatePart,
    DATEADD(day, -DATEDIFF(D, 0, DateField), DateField) as TimePart
FROM YourTable

Upvotes: 13

Related Questions