How do we can get date & time seperatly from a single datetime field in sqlserver

Question

I have few fields(empid,arrivaltime) in sqlserver. I want to get the query output as follows

79    08/11/2009     3:21PM
78    08/11/2009     3:19PM
98    08/11/2009     9:02AM
97    08/11/2009     9:00AM
96    08/11/2009     8:56AM
95    08/11/2009     8:53AM

please give me sql query for this.

Answer 1

Hi, try function DATEPART it works like this

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

Best Regards, Iordan

Answer 2

You can use the date/time formatters using the CONVERT function.

SELECT
    CONVERT(VARCHAR(10), ArrivalTime, 103) AS [ArrivalDate],
    CONVERT(VARCHAR(10), ArrivalTime, 8) AS [ArrivalTime]

Or, you can also use the UDF given here for formatting date/times by providing the format.

To get the time in AM/PM format, you'll have to tweak the code a little bit like this -

SELECT
    SUBSTRING(CONVERT(VARCHAR(20), ArrivalTime, 9), 13, 5) + ' ' +
    SUBSTRING(CONVERT(VARCHAR(30), ArrivalTime, 9), 25, 2)

Reference: SQL Server Date/Time Formatters

Answer 3

SELECT empid, CONVERT(VARCHAR(10),arrivaltime,101) as  
   date,CONVERT(VARCHAR(10),arrivaltime,108) as time FROM Table

This will display the result almost as mentioned above. The only thing I don't know is how to format the time to be displayed with AM/PM

Answer 4

Hi, Use the following query to get the exact result as you want:

select empid, convert(varchar,arrivaltime,101) ,substring( convert(varchar,arrivaltime,100),len(convert(varchar,arrivaltime,100))-6,7)