convert yyyy/mm/dd into ddmmyyyy and insert into database field

Question

0.00/5 (No votes)

See more:

hi ,

i am trying to execute below Query but its give me the error---The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

SQL

INSERT into tblXy([Date],TransDate)
VALUES(REPLACE(CONVERT(VARCHAR(10), T.TransactionDate, 103), '/', '') ,REPLACE(CONVERT(VARCHAR(10),T.TransactionDate1, 103), '/', '')

plese help me...

thanks

Posted 17-Jul-12 0:26am

vaquas

Updated 17-Jul-12 0:37am

Prasad_Kulkarni

v2

Add a Solution

Comments

Arul R Ece 17-Jul-12 6:35am

Hi..

The value of TransactionDate is from same table r another table

vaquas 17-Jul-12 6:40am

yes it is in other table

Arul R Ece 17-Jul-12 6:53am

Which column is common for both table..

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)

OriginalGriff · Answer 1 · 2012-07-17T00:34:00

Solution 1

Why? Why not convert it to a DateTime value and store that? All your code is doing is removing the parts that the DateTime conversion stuff could use to tell what part of the string you are feeding it is the day, the month and the year. If you are going to convert from a fixed format string to any other string based format, then convert to ISO (which SQL understands): "yyyy-MM-dd"

Posted 17-Jul-12 0:34am

OriginalGriff

Comments

vaquas 17-Jul-12 6:39am

because this format i need

OriginalGriff 17-Jul-12 6:49am

Why? The error message is complaining that the format you generate is invalid to fit in a DateTime column - so hand it a DateTime or a string that will convert. Not a string you assemble that won't convert.
A DateTime column doesn't have a format when stored in a DB - just a value. The idea is that to convert to a DateTime as early and possible, and convert back to a string as late as possible. That way, it is easier to deal with international formats for example.

AmitGajjar 17-Jul-12 7:01am

Perfect answer.... 5+