MSSQL Server:conversion of char or varchar to datetime type

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

You may just need this one:

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

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

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.

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

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

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.

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.

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.

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

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.

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

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

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?

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