Convert dates

  • Hi All!

    I currently have the following date stored in one of my tables:

    2008-01-01 00:00:00

    Anyone know of a way to convert to MM/DD/YYYY format?

    I need it to show 01/01/2008 and drop the time

    Thanks!

  • Dates are not stored as formatted strings in SQL Server. The client you are using will display the datetime value according to how the client is set up and could be affected by the localization setup on that PC.

    To change how a date is displayed, you can use CONVERT in your query to convert the datetime to a specified format. There is a table of formats in books online under to topic CONVERT that will show you how to get the format you are looking for.

    Note: if you are building a stored procedure or query that will be run from an application, I would recommend returning the full datetime and using the application to format the output appropriately. For example, if you are using SSRS to create a report - return the full datetime value and change the format in the report itself.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Here are some samples that I refer to frequently.

    select convert(varchar(20),getdate(),101) --1 --MM/DD/YYYY

    select REPLACE(convert(varchar(20),getdate(),101),'/','-') --2 --MM-DD-YYYY

    select convert(varchar(20),getdate(),103) --3 --DD/MM/YYYY

    select convert(varchar(20),getdate(),104) --4 --DD.MM.YYYY

    select convert(varchar(20),getdate(),105) --5 --DD-MM-YYYY

    select convert(varchar(20),getdate(),106) --6 --DD MMM YYYY

    select convert(varchar(20),getdate(),107) --7 --MMM DD, YYYY

    select convert(varchar(20),getdate(),108) --8 --CURRENT TIME HH:MM:SS

    select convert(varchar(30),getdate(),109) --9 --CURRENT DATE AND TIME MMM DD YYY H:MM:SS AM/PM

    select convert(varchar(20),getdate(),110) --10 --MM/DD/YYYY

    select convert(varchar(20),getdate(),111) --11 --YYYY/MM/DD

    select convert(varchar(20),getdate(),112) --12 --YYYYMMDD

    select convert(varchar(30),getdate(),113) --13 --CURRENT DATE AND TIME DD MMM YYY HH:MM:SS:MMM

    select convert(varchar(30),getdate(),114) --14 --CURRENT TIME HH:MM:SS:MMM

    select convert(varchar(30),getdate(),120) --20 --CURRENT DATE AND TIME YYYY-MM-DD HH:MM:SS

    select convert(varchar(30),getdate(),121) --21 --CURRENT DATE AND TIME YYYY-MM-DD HH:MM:SS:MMM

  • Thanks, these should help!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply