Convert String to DateTime

[crs27]

Hai,

Im new to sql server...
I want to convert a field in a table to DateTime.

The column(Column Name-DDateTime , Data type String) has data in this format 01-04-2008 15:12:52
i want to get records in a particular datetime range.Do not no how to proced.

usually i can write a query like this
select DDateTime from table_name where DDateTime between '01/27/2008 01:23:32' and '01/29/2008 01:23:32'

as the column's datatype is string im facing problem..

[ck9663]

use CAST or CONVERT . You have to make sure the arguments that you're using for comparison have the same data type or can be explicitly converted.

-- CK

[crs27]

use CAST or CONVERT . You have to make sure the arguments that you're using for comparison have the same data type or can be explicitly converted.

-- CK

but i did nt no how to use the convert function in the where clause..

The string formate is 'day-month-year hr:min:sec' in the table can u please help me to write the query..

Thanks for the quick reply

[ck9663]

Here's SELECT and the WHERE. Here's the BETWEEN and here are some samples.

-- CK

[crs27]

Here's SELECT and the WHERE. Here's the BETWEEN and here are some samples.

-- CK

thanks for the help..
Shal get back if still got problem

[crs27]

thanks for the help..
Shal get back if still got problem

it did nt help me much..
as the example their the field was of datetime.But my sinario is different i was to get records between a datetime range and tht particular field is of String datatype.

i wrote a query something like this

SELECT * from vts3 where convert(datetime,dubaidatetime)between '31-03-2008 01:00:00' and '02-04-2008 01:00:00'

its giving me an exception
Error code 242, SQL state S0003: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

plz help

[ck9663]

use CAST on your field and the dates on the BETWEEN operator. Check your syntax.

-- CK

[gpl]

Try this

SELECT * 
from vts3 
where convert(datetime,dubaidatetime, 105) between 
convert(datetime,'31-03-2008 01:00:00', 105) and 
convert(datetime,'02-04-2008 01:00:00', 105)
 

This puts your string dates into datetime format, so that they can be properly compared .... the 105 means the date is in dd-mm-yyyy format, check out Convert in BOL
Graham