asked on

Date in SQL

Hi All,

I have a filed that is data time in the database. I want this field to show in one column as date and different column as time.

right now it is showing

2011-09-26 08:00:00
Date Time
it should show like this 09/26/2011 8:00 Am
the field name is : Appointment.ActualDateTime

SELECT Appointment.Id, Staff.FirstName + ' ' + Staff.LastName AS Staff_Name, Appointment.ActualDateTime,
from......
___________________

second part to this: when I input the parameter to run data from one date to another, I would like to insert the date only. so if I am looking for 08/01/2012 data, it should give me everything fpor that date, right now, it is trying to filter the date and hour. What I have been doing is inputting >=08/01/2012 and <08/02/2012 to get all appointments.

Any advise.....

Jared_S

This should solve both of your problems.

Date = convert(varchar,Appointment.ActualDateTime,101)
Time = (varchar,Appointment.ActualDateTime,108)

The dates will be in a varchar format. SQL will convert them for you automatically in your query, but you may want to do so explicitly.

ASKER CERTIFIED SOLUTION

Zberteoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

real9555

ASKER

hi thank you for the feedback but if I wanrt to show Am and PM in the below,

Time = (varchar,Appointment.ActualDateTime,108)

Jared_S

You have a couple of options that I know of to display AM/PM.

You could convert it to a date/time like this:

convert(datetime,convert(varchar,Appointment.ActualDateTime,108))

Open in new window

(this will be easier to work with when it comes to data manipulation, but it stores a datetime placeholder - 01/01/1900 or 12/31/1899 - in front of the time)

or

substring(convert(varchar,Appointment.ActualDateTime),charindex(':',convert(varchar,Appointment.ActualDateTime))-2,7)

Open in new window

which is a varchar datatype, not a time, but doesn't include the datetime placeholder

Zberteoc

Here is a link to tell you about all the formats you can use with CONVERT function:

http://msdn.microsoft.com/en-us/library/ms187928.aspx