How to get a time and Date Separately?

Question

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

Answer 1

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]

Answer 2

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

Answer 3

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

See this reference for all formats

Answer 4

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]