[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

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.....
0
real9555
Asked:
real9555
  • 2
  • 2
1 Solution
 
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
 
ZberteocCommented:
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now