# Miscellaneous > Ask an Expert >  MSSQL Server:conversion of char or varchar to datetime type

## srimamidi

Hi

I have a problem while converting the char to datetime type in MSSQL Server 200/2005. 

I want to retrieve the date values from table in datetime data type in a particular format like 'yyyydd' or 'yyyy/dd' similar to _oracle's TO_DATE function_. 

For this I tried with following query,

*SELECT CONVERT(datetime, CONVERT(varchar(6), date1, 112), 112) from sql_dm1*

But I got the following error while executing the above query:
*The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.*

Please help me in this regard.

Thanks
Sridhar

----------


## rmiao

You may just need this one:

SELECT CONVERT(varchar(6), date1, 112) from sql_dm1

----------


## srimamidi

Datatype of *return value*  from the above function is *varchar/char*  but my requirement is, it should be *Datetime datatype*.

----------


## nosepicker

If your data is only 'yyyydd' or 'yyyy/dd', what month is it?  It's going to be pretty hard to convert that data without knowing what month it is.

----------


## srimamidi

its dynamic,  I mean the values stored in the database are multiple rows.

----------


## rmiao

Can you post some sample data and the result you like to see?

----------


## srimamidi

I have the table with following data:

	date1

	2006-04-13 00:00:00
	2006-12-21 00:00:00
	2006-01-25 00:00:00
	2006-10-27 00:00:00
	2007-08-29 00:00:00

And I want the result should be :

	2006-04
	2006-12
	2006-01
	2006-10
	2007-08

By using  _SELECT CONVERT(varchar(7), date1, 112) from sql_dm1_ I can get the above result, but the return value is now string type not Date datatype. If I apply sorting on it sorting is done on string not Date datatype. This is the actual problem I am facing.

----------


## nosepicker

You can't have the results you want with a datetime datatype.  The datetime datatype requires all elements of a date (year, month, and day).  And I don't see what's the difference between sorting your results as string vs. date.  The results should be the same.  I think you're going to have to explain what you want to do in more detail.

----------


## srimamidi

I am using the results in a web application. The user can sort the results, as the result values are in string type the values sorted are: first comes april then aug then february then january.....
not by month.

This is the problem I am having in the application.

----------


## rmiao

Where do you sort result? Didn't see your issue in sql result with order by clause.

----------


## nosepicker

Show us EXACTLY the data you are trying to convert to datetime.  Looks like you are starting with month names ("January" instead of "01").  Please give us more information, or else you make it hard for us to help you.

----------


## fun4uall

The datetime type by default needs all the three parts.  The alternative could be you get the data in whatever format SQL gives you, then in the front-end you can convert and sort it the way you need

----------


## gbshashi

Hi Srimamidi,
try this query..
SELECT CAST(YEAR(DATE1) AS VARCHAR(4)) + '-' + CAST(MONTH(DATE1) AS VARCHAR(2))  AS DATE1 FROM TABLE_NAME

----------


## ujupanmester

Hy,

I have a few table and one of the row is date but in in varchar format.(now the format is mm/dd/yyyy).

Now i want to convert the row in datetime format mssql but like this(dd.mm.yyyy)
I can change this?

----------


## rmiao

Possible with style 104, take look at 'CAST and CONVERT (Transact-SQL)' in books online.

----------

