real9555
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.....
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.....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi thank you for the feedback but if I wanrt to show Am and PM in the below,
Time = (varchar,Appointment.Actua lDateTime, 108)
Time = (varchar,Appointment.Actua
You have a couple of options that I know of to display AM/PM.
You could convert it to a date/time like this:
(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
which is a varchar datatype, not a time, but doesn't include the datetime placeholder
You could convert it to a date/time like this:
convert(datetime,convert(varchar,Appointment.ActualDateTime,108))
(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)
which is a varchar datatype, not a time, but doesn't include the datetime placeholder
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
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Date = convert(varchar,Appointmen
Time = (varchar,Appointment.Actua
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.