How to convert varchar into datetime

Question

Post reply

How to convert varchar into datetime

eko indriyawan

Hall of Fame

Points: 3316
More actions
October 15, 2005 at 1:50 am

#110223

hallo..........

i have a problem like this..........

i have a table, we call it as ConvertTable
and there are several filed in the ConvertTable ; Date | Time | Message

Datatype :

Date -->> Varchar(25)
Time -->> varchar(25)
Message -->> varchar(500)

i want to show data from ConvertTable that datattype from field Date as datetime

i just try with this query :

SELECT cast(Date as Datetime) as Date from ConvertTable

when i run, i get error message like this :

[Microsotf][ODBC SQL Server Driver][SQL Server]The Convertion of char data type to a datetime type resulted in an out-of-range datetime value.

i confused with this condition.

btw, anyperson who can help me in solving this problem ????

thanxs...

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 1

The Convertion of char data type to a datetime type resulted in an out-of-range datetime value.

-> results in a date before 1753

Look up CONVERT for an extra format parameter.

apc.marcelino Valued Member Points: 63 More actions · Answer 2

Take a look of what language are your database using ??? try using the convert function with the format date parameter.

Pothiraja Valued Member Points: 56 More actions · Answer 3

Dear Friend,

Use this query

Syntax for Convert functions

=====================

CONVERT([datatype],[fieldname],[formattype])

SELECT CONVERT(datetime, date,101) FROM ConvertTable

I hope you can get the result what u expect.

M.Pothiraja

Vladan SSC-Insane Points: 22072 More actions · Answer 4

As far as I can remember, you get this error message if one of the strings to be converted:

1) does not result in a date at all (incorrect entry)

2) does not result in a date in a valid timespan for datetime (e.g. year 1645)

3) does not translate to a correct date, given your settings (e.g. entry as RRRR/MM/DD and environment setting RRRR/DD/MM - attempt to translate some dates to a month 21 etc.)

Both CAST and CONVERT are acceptable, but if you have problems, try CONVERT. There are more possibilities to influence the result using parameters - see BOL for more detail.

dmanju Say Hey Kid Points: 685 More actions · Answer 5

The above response indicated the exact scenario you might be running into. However, to check which one of these conditions you are encountering, you could open a cursor and check which particular date is causing the out of range date.

eko indriyawan Hall of Fame Points: 3316 More actions · Answer 6

thxs for ALL........

i have get the answer...

now i have a problem, how to show a "tab" in record

my sentence like this "EKO INDRIYAWAN"

when i insert into record become "EKOINDRIYAWAN"

anyperson who can help me ???

rockingpravi SSC Journeyman Points: 86 More actions · Answer 7

Hi,

Could you please try this example and let me know if you are able to convert date to varchar without any issues.

eg: Create table #temp(currDate varchar(20))

insert into #temp values(CONVERT(varchar(10),getdate(),121))

SELECT cast(currDate as Datetime) as currDate from #temp

dcs09 Right there with Babe Points: 718 More actions · Answer 8

The query :- SELECT CONVERT(datetime, dt,101) FROM [TestDB].[dbo].[test_date]

gave me the error:-

Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 9

@dcs.. - why did you choose the parameter setting '101'? Look up the command (Books OnLine [BOL], or brute force via Google) and make certain you understand your date formatting correctly. If you specify the wrong format, that will yield your error. Otherwise, it may be a data cleansing challenge.

dcs09 Right there with Babe Points: 718 More actions · Answer 10

steve smith-401573 (9/15/2011)
@dcs.. - why did you choose the parameter setting '101'? Look up the command (Books OnLine [BOL], or brute force via Google) and make certain you understand your date formatting correctly. If you specify the wrong format, that will yield your error. Otherwise, it may be a data cleansing challenge.

Hi Steve,

Thanks for you response. I used 101 for mm/dd/yyyy format. Anyways its working now. Many Thanks

Cheers,

DCS