putting a date string made from string builder in an sql query

[beginningsysdev]

Hi,
i'm currently making an app that needs to run a query between two dates. this app will run automatically, so i need to put the dates as today and yesterday, essentially.

        Dim str As New System.Text.StringBuilder()
        Dim time As String
        str.Append("'")
        str.Append(Date.Now.Year.ToString)
        str.Append("-")
        str.AppendFormat("{0:MM}", Date.Now.Month.ToString)
        str.Append("-")
        str.Append(Date.Now.Day.ToString)
        str.Append(".")
        str.AppendFormat("{0:hh}", Date.Now.Hour.ToString)
        str.Append(".")
        str.AppendFormat("{0:mm}", Date.Now.Minute.ToString)
        str.Append(".")
        str.AppendFormat("{0:ssssss}", Date.Now.Second.ToString)
        str.Append("'")
        time = str.ToString
        Console.WriteLine(time)

That is the string I made, and now I need to put it in the sql string.

'" & time & "'"

That is how I have it in my sql string, but it just crashes when I run it.

Any suggestions?

[Joseph Martell]

I think that I see 2 problems with your code. The first is that you have 2 sets of ' to delimit your date string.

You start and end you string builder with the lines:

str.Append("'")

and then you insert it into you SQL statement as such:

"'" & time & "'"

so your date/time string looks like (if I follow your string builder logic):

''2010-06-23.09.01.57''

The second is your date/time format. I get an error when attempting to insert a date-time in that format into SQL Server.

Out of curiosity, why are you using a string builder to construct your date time string instead of something like:

        Dim myDate As Date = Date.Now
        myDate.ToString("G")
 

which gives me a timestamp of:
6/24/2010 9:12:11 AM

If the fractions of a second are necessary you can still use the date datatype's ToString method, but supply the following format:

myDate.ToString("yyyy-MM-dd hh:mm:ss.ffffff")

which gives me a timestamp of:
2010-06-24 09:15:17.113125