How to get and Format current date in MSSQL

Posted on 2008-11-10
Last Modified: 2012-05-05
In acces i can format the current date like so Format(Now(),'dd/mm/yyyy')

what can i use to make it work formssql ? any ideas

Many thanks
Question by:david_88
    LVL 39

    Expert Comment

    select convert(char(10), getdate(),101)
    LVL 59

    Expert Comment

    by:Kevin Cross
    You get the current date using GETDATE() and you can use the CONVERT function to change to specified string format.
    LVL 17

    Expert Comment

    SELECT CONVERT(datetime,GETDATE(),103)  -- 2008-11-10 13:04:35.603

    SELECT CONVERT(char(10),GETDATE(),103) --  10/11/2008
    LVL 59

    Expert Comment

    by:Kevin Cross
    HoggZilla's is what you want to use for DD/MM/YYYY format -- 103 -- other values are listed on the chart I provided.

    Good luck!

    Expert Comment

    You can use a number of formats style for converting a Date value to retrieve either the date part or time or both, and here are some of them:

    Select Convert(Varchar,GetDate(),101)

    Select Convert(Varchar,GetDate(),112)

    Select Convert(Varchar,GetDate(),114)

    Select Convert(Varchar,GetDate(),121)

    Additionally, take a look at the URL's below for further examples
    LVL 59

    Expert Comment

    by:Kevin Cross
    @rameshvelayudhan, you posted some really great examples but none of those formats to DD/MM/YYYY.  

    I already posted a link to all the possible formats, but http:#22923864 is the answer.
    LVL 39

    Expert Comment

    You would be best served to leave the formatting of information to your front end (or report) and leave the data in the datetime format for export from SQL Server.
    LVL 51

    Accepted Solution

    There is only one that does DD/MM/YYYY and that is 103, trouble is it will also do time, so, you have to get rid of that first... or... convert to a string field (and loose date arithmetic). And hogzilla posted it correctly first, but didn't explain...

    so, for "current" datetime, we use :    getdate()

    then using the convert function to express that as a format :

    select convert(varchar(10), getdate(), 103)    -- gives 17/11/2008 - but no longer a recognise datetime column, so if you want to do arithmetic, best to use international format ie:

    select convert(varchar(8), getdate(), 112)

    with that you can still add a day, or a time - for example tomorrow...

    select convert(varchar(8), getdate(), 112) + 1  -- cannot do that with format 103

    or for end of day...

    select convert(varchar(8), getdate(), 112) + ' 23:59:59'

    but if we want to add time as well, then we have to use date functions for part of it (can append time, or add days, but not both without some help)

    select dateadd(dd,1, convert(varchar(8), getdate(), 112) + ' 23:59:59' )

    The Format number really just tells the convert function how to format the string. If there are no strings, then it really isn't needed (ie it is in datetime already). That format number must match exactly otherwise you do get errors - unless of course it is international format of yyyymmdd  which is always understood and can be explicitly converted. Similarly, format 106 which is dd MMM yyyy (e.g. 17 Nov 2008) is also understood, and the one I tend to use all the time (or the international), however, is constrained by language settings (as in different languages do spell the months differently).

    LVL 39

    Expert Comment

    The problem with doing "MATH" on the format 112 without converting it to a datetime is the last day of the month will increment to an invalid date.  

    See below:

    declare @TheDate datetime
    set @TheDate = convert(datetime, '06/30/2008',101)
    select @theDate, convert(varchar(8), @TheDate, 112) + 1, @TheDate+1

    Open in new window


    Author Closing Comment

    thanks most detailed answer and helped a great deal thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now