Convert Text to DateTime Format

Question

0.00/5 (No votes)

See more:

I have a field in my SQL Server 2008 table as follows:

Field Name: Dt_Tm
Format: nvarchar(20)
Example: 10 Oct 2012@0715

I have created the following in a SELECT statement using this field:

LEFT(Dt_Tm, 11) + ' ' + SUBSTRING(Dt_Tm, 13, 2) + ':' + RIGHT(Dt_Tm, 2) + ':00'

This works and I get '10 Oct 2012 07:15:00'. But when I try to INSERT this into a new table field that is formatted 'smalldatetime', I get the following error:

"Conversion failed when converting character string to smalldatetime data type"

Can anyone help me with this problem.

Thank you.

Posted 8-Nov-12 0:24am

Member 8665486

Add a Solution

Comments

Richard MacCutchan 8-Nov-12 6:41am

Why are you storing datetime as characters and not using the DateTime type that SQL kindly provides for you? See also http://msdn.microsoft.com/en-us/library/aa258277(SQL.80).aspx.

Member 8665486 8-Nov-12 7:35am

The data is imported from a system that doesn't use a standard datetime format; so I'm working from an import table and trying to get it into my functional table as smalldatetime. Thanks

Om Prakash Pant 8-Nov-12 6:56am

do you have all the data in the same format?

Member 8665486 8-Nov-12 7:36am

All of the data is in the exact same format in my import table as text. I now need to get it into my working table as a usable date/time field. Thank yo.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shanalal Kasim · Accepted Answer · 2012-11-08T00:50:00

Solution 1

try:

SQL

convert(date,LEFT(Dt_Tm, 11) + ' ' + SUBSTRING(Dt_Tm, 13, 2) + ':' + RIGHT(Dt_Tm, 2) + ':00')

Now convert function will return date value

Posted 8-Nov-12 0:50am

Shanalal Kasim

Comments

Member 8665486 8-Nov-12 7:51am

I am still getting the same error. In both a SELECT query and in the INSERT query. Is there another option? Thank you.

Shanalal Kasim 8-Nov-12 8:03am

please try this sql

select convert(date,LEFT(Dt_Tm, 11) + ' ' + SUBSTRING(Dt_Tm, 13, 2) + ':' + RIGHT(Dt_Tm, 2) + ':00') from yourtable

In my end this sql is working and return 2012-10-10

Member 8665486 8-Nov-12 8:05am

I changed the format to:
CONVERT(VARCHAR(23), LEFT(Dt_Tm, 11) + ' ' + SUBSTRING(Dt_Tm, 13, 2) + ':' + RIGHT(Dt_Tm, 2) + ':00', 120)

And this works in the SELECT query, but does not work in the INSERT query. I still get the same error.

Shanalal Kasim 8-Nov-12 8:11am

please provide full code with table structure

Member 8665486 8-Nov-12 8:22am

Here is the full SQL. I really appreciate your help.

INSERT INTO tblAppointments_Final
(
ApptDate,
ApptTime,
FacilityID,
Clinic,
ProviderName,
ApptType,
ApptStatus,
Wkday,
SurgeryType,
SurgeryDays,
CostCenter,
FCC,
OpenAppts,
ProvSpec,
ApptDt_Tm,
RunDt_Tm,
Uni
)
SELECT CONVERT (VARCHAR(24),LEFT([Dt_Tm],11),113) As ADt,
RIGHT([Dt_Tm],4) As ATm,
t2.FacilityID,
t1.Clinic,
t1.Provider,
t1.ApptType,
t1.ApptStat,
t1.Wkday,
t1.SurgeryType,
isnull (t1.SurgeryDays, '') As mySurgeryDays,
t1.CostCenter,
RIGHT(t1.CostCenter,3) As FCC,
(t1.MBk - isnull (t1.Bk, '0')) As OpenAppts,
t3.Class,
CONVERT(VARCHAR(23), LEFT(Dt_Tm, 11) + ' ' + SUBSTRING(Dt_Tm,13,2) + RIGHT(Dt_Tm,2),120),
CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, GETDATE(), 121), 4+1+2+1+2)
+ ' 07:00') As RunDtTm,
t1.Dt_Tm + t1.Provider + t1.Clinic + t1.ApptType As uni

FROM tblAppointments_Import t1
INNER JOIN tblClinic t2 ON t1.Clinic = t2.Clinic
INNER JOIN tblProvider t3 ON t1.Provider = t3.Provider

Shanalal Kasim 8-Nov-12 8:38am

change this line-> CONVERT(VARCHAR(23), LEFT(Dt_Tm, 11) + ' ' + SUBSTRING(Dt_Tm,13,2) + RIGHT(Dt_Tm,2),120),
to-> convert(varchar,LEFT(Dt_Tm, 11) + ' ' + SUBSTRING(Dt_Tm, 13, 2) + ':' + RIGHT(Dt_Tm, 2) + ':00')

Member 8665486 9-Nov-12 1:08am

Thank you shanalakasim---you saved me hours of working on this problem,

Your solution worked: CONVERT(VARCHAR, LEFT(Dt_Tm, 11) + ' ' + SUBSTRING(Dt_Tm, 13, 2) + ':' + RIGHT(Dt_Tm))

Shanalal Kasim 9-Nov-12 1:11am

Welcome