Date in SQL

Posted on 2012-09-14
Last Modified: 2012-10-02
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,

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.....
Question by:real9555
    LVL 12

    Expert Comment

    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.
    LVL 26

    Accepted Solution

    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.

    Author Comment

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

    Time = (varchar,Appointment.ActualDateTime,108)
    LVL 12

    Expert Comment

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

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


    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)



    Open in new window

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

    Expert Comment

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

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now