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.....
real9555Asked:
Who is Participating?
 
ZberteocConnect With a Mentor Commented:
If you are using SQL server 2008 you can cast the column to type date to get only the date part and to type time to get only the time part:

select getdate() dte_tme, cast(getdate() as date) dte, cast(getdate() as time) tme

dte_tme	dte	tme
2012-09-14 11:38:34.270	2012-09-14	11:38:34.2700000

Open in new window


So in your case you can use the query:

select * from Appointment where cast(ActualDateTime as date)='2012-08-01'

Open in new window


Make sure you use the YYYY-MM-DD date format when you pass the parameter.
0
 
Jared_SCommented:
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.
0
 
real9555Author Commented:
hi thank you for the feedback but if I wanrt to show Am and PM in the below,

Time = (varchar,Appointment.ActualDateTime,108)
0
 
Jared_SCommented:
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
0
 
ZberteocCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.