CONVERT(char(10),[DATE_FIELD], 110) Question

Posted on 2009-04-29
Last Modified: 2012-05-06
I was wondering if anyone knows why the DATA_TYPE has to be char(10). I tried to use other data types and each type I tried produced an error. For example, I used smalldatetime(4), smalldatetime, and datetime(8) of which none worked.

Thank you in advance.
CF Jordan
Question by:cfjordan
    LVL 1

    Expert Comment

    Its because to convert a datetime field to 01/01/2009 format its a varchar datatype, its really just plain text.
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    CONVERT(char(10),[DATE_FIELD], 110 )  is usually used to trim the time part, instead of CHAR(10) if you put a smalldatetime or datetime, it will again get converted to datetime value, with 00:00:000 as the time part
    thats why u r recommended to use char datatypes
    LVL 7

    Accepted Solution

    Let's look at your example:
    CONVERT(char(10),[DATE_FIELD], 110)

    Note that you are using the 110 style. this returns a character string in a specified format.

    Char(10) is not really the only type you could use, but it would have to be a character of some type. Such as varchar(20), nvarchar(20), etc.
    LVL 1

    Author Comment

    Oh, I see, as long as I use a character field, I should be okay. My subsequent question then is by using a character field to convert the date field will that cause any problems when evaluating the date? For example, if I need to perform some sort of calculation on the date field to find a period of time, will I still be able to even though the field type is a character?

    I am increasing the point value since I asked another question.
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    while comparing this 'char' date with another column of 'datetime', sql will implisitly convert this char date to DATETIME before the comparison
    LVL 1

    Author Closing Comment

    Thank you for your help, I know have a deeper understanding of the convert function.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    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…

    746 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

    14 Experts available now in Live!

    Get 1:1 Help Now