By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,450 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

Convert String to DateTime

P: 40
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..
Apr 3 '08 #1
Share this Question
Share on Google+
7 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Apr 3 '08 #2

P: 40
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
Apr 3 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Here's SELECT and the WHERE. Here's the BETWEEN and here are some samples.

-- CK
Apr 3 '08 #4

P: 40
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
Apr 3 '08 #5

P: 40
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
Apr 3 '08 #6

ck9663
Expert 2.5K+
P: 2,878
use CAST on your field and the dates on the BETWEEN operator. Check your syntax.

-- CK
Apr 3 '08 #7

100+
P: 152
gpl
Try this

Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. from vts3 
  3. where convert(datetime,dubaidatetime, 105) between 
  4. convert(datetime,'31-03-2008 01:00:00', 105) and 
  5. convert(datetime,'02-04-2008 01:00:00', 105)
  6.  
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
Apr 3 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.