Post reply

Displaying a date

  • April 10, 2008 at 6:06 am

    #128485

    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

  • April 10, 2008 at 6:16 am

    #800915

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

  • April 10, 2008 at 6:40 am

    #800932

    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

  • April 14, 2008 at 6:53 am

    #802031

    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

  • April 15, 2008 at 9:44 am

    #802664

    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 4 (of 4 total)

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