• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 742
  • Last Modified:

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

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
0
Gary Jones
Asked:
Gary Jones
3 Solutions
 
smyers051972Commented:
Its because to convert a datetime field to 01/01/2009 format its a varchar datatype, its really just plain text.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
twolCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gary JonesDirector of Information TechnologyAuthor Commented:
twol:
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.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
while comparing this 'char' date with another column of 'datetime', sql will implisitly convert this char date to DATETIME before the comparison
0
 
Gary JonesDirector of Information TechnologyAuthor Commented:
Thank you for your help, I know have a deeper understanding of the convert function.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now