Converting varchar to DateTime

Hi,

I wanted to convert the varchar to date time and here is what i am doing

DECLARE @.dt VARCHAR(20)

SET @.dt = '20070111' -- YYYYMMDD format

select CONVERT(datetime, @.dt, 120)

This works perfectly fine and the result would be- 2007-01-11 00:00:00.000

But if i changed my datetime format from YYYYMMDD to YYYYMMDDHHMM then this is failing and throwing

"Conversion failed when converting datetime from character string."

Can any one please let me know how do we achieve this?

~Mohan

YYYYMMDDHHMM is not recognized as a valid datetime string. For example, YYYMMDD HH:MM works.

|||

This is the Convert sintax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The first parameter data_type is the required convertion type, of course including the length if required. The second parameter is the expression to convert, and to endding the last parameter is used to define the style in that you are passing the "expression" parameter.

In your code, the style parameter says 120 that corresponds to a ODBC Canonical format in this format: yyyy-mm-dd hh:mis.

Then, ?Does because a string with the format yyyymmdd can be converted to string?:

The YYYYMMDD is widely recognized ODBC String Format that can be used to convert a string to a SQL Server DateTime format. When you use this format, the convert function ignores the last parameter, because it's a standard format. Instead, YYYMMDDHHMM is not a SQL Server recognized format, by this you can not use this.

I recommend to you, to pass strings in the yyyy-mm-dd hh:mis format to be recognized by the CONVERT or CAST functions in SQL server.