How to Extract Only Date Without Time from SQL DateTime Field

Data and Databases
1

I’m working with a database table that stores datetime values, but I need to display only the date portion without the time component. Currently, I’m getting results like ‘2010-06-08 11:03:25.797’, but I want just the ‘2010-06-08’ format. I need to modify my stored procedure to return clean date values. Here’s what I’m working with:

CREATE PROCEDURE [dbo].[spGetOrdersByDateRange]
(
    @StartDate DATE,
    @EndDate DATE, 
    @CompanyID INT = NULL
)
AS
BEGIN
    SELECT 
        @CompanyID,
        comp.company_id,
        vendor.vendor_code,
        contact.full_name AS VendorName,
        ord.order_number,
        ord.product_sku,
        (customer.lastname + ', ' + customer.firstname) AS CustomerName,
        ord.order_date,
        ord.ship_date,
        ord.quantity,
        ord.total_amount,
        ord.payment_terms,
        ord.tax_amount,
        ord.deposit_amount
    FROM tVendor vendor
        INNER JOIN tContact contact ON vendor.contact_id = contact.contact_id
        INNER JOIN tOrder ord ON vendor.vendor_id = ord.vendor_id
        INNER JOIN tCompany comp ON vendor.company_id = comp.company_id
        INNER JOIN tContact customer ON customer.contact_id = ord.customer_id
    WHERE comp.company_id = @CompanyID 
        AND ord.order_date BETWEEN @StartDate AND @EndDate
END

What’s the best way to format these datetime columns to show only the date part?

2

Nice approaches! Quick question tho - what’re you planning to do with these date values? Any calculations or filtering coming up? If you need more control over the format, FORMAT(ord.order_date, 'yyyy-MM-dd') might work better. What kind of reports are you building?

3

you can also use CONVERT function: CONVERT(DATE, ord.order_date) and CONVERT(DATE, ord.ship_date). takes away the time and gives you the date only. super easy!

4

The CAST function is another clean way to handle this. Just modify your SELECT to use CAST(ord.order_date AS DATE) and CAST(ord.ship_date AS DATE) - it’ll strip the time and give you just the date. This works great when you need to keep the DATE data type for other operations or comparisons. I’ve used this across different SQL Server versions and it plays nice with stored procedures. Performance hit is basically nothing, and your code stays readable while showing exactly what you need for reports.