Displaying a date

  • daniel-703741

    Valued Member

    Points: 68

    Hello all,

    I am learning queries in SQL Express and I would like to display the dates in the resultset without the time. Since I come from the Access world I have these little wishes.

    For example one of the date appears like:

    2008-04-10 22:04:39.733

    I looked at DatePart and I did a few researches on my own. But I can't find what I am looking for.

    I would like to format the date in the query so that the date appears say: 10 April 2008

    I know that this is not Access but there is a query I would use:

    Format(DateField,"MediumDate")

    Thanks for your kind help.

    Daniel

  • Anipaul

    SSC-Insane

    Points: 24681

    Try this function: TEXT(TODAY(),"m/d/yy")

  • dragos_sv

    Default port

    Points: 1477

    see convert function:

    select convert(varchar(30),getdate(), 106) does almost what you want

    there are different formats, maybe you find one that suits you

    or... if you really want the full month name use datename:

    SELECT

    convert(varchar(2),datepart(d,getdate()))

    + ' '

    + convert(varchar(10),datename(month, getdate()))

    + ' '

    + convert(char(4),datepart(yy, getdate())) as mydate

    dragos

  • daniel-703741

    Valued Member

    Points: 68

    Thank you very much for the help dragos. The gentleman above you didn't know what he was talking about.

    The expression CONVERT(Varchar(30), DateField, 106) worked very well. Now I will just need to understand what the 106 stands for but with the Help file this shouldn't be too difficult.

    Thanks again dragos,

    Daniel

  • Key DBA

    SSCertifiable

    Points: 6029

    daniel,

    Welcome to Date and Time fun with SQL Server!

    Dates (and Times) are a controversial topic in the both the way they are stored in a database as well as how to get it to display for your given query.

    Many, many posters to this and other database forums frequently ask about storing times/dates as non-date/time datatypes ... and the resulting discussions are interesting reads.

    Retrieving and displaying date/time data is just as, um, enlightening in the forums.

    This handy little chart from SQL Server 2005 Books Online explains the mysterious "106" format.

    SQL Server 2005 Books Online (September 2007)

    CAST and CONVERT (Transact-SQL)

    http://msdn2.microsoft.com/en-us/library/ms187928.aspx

    My best advice for you is to spend some time working with the storage aspects of the datatypes and being able to manipulate the display of the dates (and times) until the "fog lifts". You will quickly be able to recall where to look up the syntax for formatting.

    It looks like you are passed your first problem.

    Happy T-SQLing,

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Viewing 5 posts - 1 through 5 (of 5 total)

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