SQL Insert and Replace?

Question

I have an SQL statement I run for my website that inserts data into an access database. Is there a way during the insert to replace a space " " if one of the date textboxes contains a space? I need to do this because it throws off my date queries if there is a space in one of the columns?

INSERT INTO tblOpen (DateColumn1, DateColumn2) Values (@Value1, @Value2)

I need to ask you. Do you store a date value inside a string database field? — Steve
– Steve, Commented May 12, 2014 at 14:33
And here are the roots of your problems. A date should be stored as a date, not as a string. Then you will avoid any kind of problems in querying on that dates. — Steve
– Steve, Commented May 12, 2014 at 14:40

Mehul Vaghela · Accepted Answer · 2014-05-12 15:01:39Z

2

You should use the 'datetime' type for your DateColumn. It solves all your problem. It is good to use proper variable for proper field.

answered May 12, 2014 at 15:01

Mehul Vaghela

4785 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michele · Accepted Answer · 2014-05-12 14:43:09Z

1

If you mean heading and trailing spaces, then:

myString = myString.Trim()

in your vb.net code will be fine. Even though I would follow Steve's suggestion and converting to date.

answered May 12, 2014 at 14:43

Michele

8,7937 gold badges51 silver badges74 bronze badges

Comments

user2316116 · Accepted Answer · 2014-05-12 16:12:58Z

As question was about replace, you can either use Replace()

INSERT INTO tblOpen (DateColumn1, DateColumn2) 
    Values (REPLACE(@Value1, ' ', ''), @Value2)

or LTrim() and RTrim()

INSERT INTO tblOpen (DateColumn1, DateColumn2) 
    Values (LTRIM(RTRIM(@Value1)), @Value2)

However if datatype is a datetime then it makes sense to convert to that type using

DateTime d = Convert.ToDateTime(TextBox1.Text);
SqlParameter p = command.Parameters.Add("@Value1", System.Data.SqlDbType.DateTime);
p.Value = d;

Collectives™ on Stack Overflow

SQL Insert and Replace?

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related