Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-04-29
6
Medium Priority
?
723 Views
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
0
Comment
Question by:Gary Jones
6 Comments
 
LVL 1

Expert Comment

by:smyers051972
ID: 24265048
Its because to convert a datetime field to 01/01/2009 format its a varchar datatype, its really just plain text.
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 200 total points
ID: 24265062
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
 
LVL 7

Accepted Solution

by:
twol earned 300 total points
ID: 24265076
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 1

Author Comment

by:Gary Jones
ID: 24265149
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 200 total points
ID: 24265175
while comparing this 'char' date with another column of 'datetime', sql will implisitly convert this char date to DATETIME before the comparison
0
 
LVL 1

Author Closing Comment

by:Gary Jones
ID: 31576180
Thank you for your help, I know have a deeper understanding of the convert function.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

810 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