convert from date formate mm/dd/yyyy to dd/mm/yyyy

Hi guys when i am doing an sql query  for the date is it comming out with the us formate meaning it wont find the information i am look here is the code can you please help me get it in good old aussie format thanks guys
Private Sub ListView2_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView2.MouseClick
        Dim rs As New ADODB.Recordset
        Dim sql As String
        Dim datesel As String
        datesel = ListView2.SelectedItems.Item(0).SubItems(1).Text
        sql = "select * from [Student_Outcomes] where [OutcomeDate]  = #" & datesel & "# And [StudentID] = " & TextBox7.Text
        Debug.Print(sql)
        rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly)
        studentnotes.Text = ""
        If Not (rs.EOF And rs.BOF) Then
            If Not IsDBNull(rs.Fields("OutcomeNotes").Value) Then
                studentnotes.Text = rs.Fields("OutcomeNotes").Value
            End If
        End If
        rs.Close()
    End Sub

Open in new window

awolarczukAsked:
Who is Participating?
 
Wayne Taylor (webtubbs)Commented:
No, that would cause the same problem as before. You need to convert the US dates (MM/dd/yyyy) to 'proper' dates, which is what the code I suggested does.

But I just noticed something in your screenshot post. If your date is "19/06/2007", it's already in the correct format, so what's the problem?? The date returned is in a different format?? How do you load the dates into the listview? All you'd need to do is format them however you need them.

Wayne
0
 
kennethfineCommented:
Hi there,

I see you are using VBScript. See this page, esp. under the "international" section. It will walk you through exactly what you need to know:

http://www.codetoad.com/asp/format_date_time.asp

If you need help adapting this to your script, just let me know.

Happy Programming!
0
 
Wayne Taylor (webtubbs)Commented:
Assuming VB.Net, not VB Script, try the below code.

Also, when storing dates into a database, ALWAYS use dd-MMM-yyyy format. This avoids any future problems, and ensures the dates are stored correctly.

Wayne
    Dim datesel As Date
    datesel = Date.ParseExact(ListView2.SelectedItems.Item(0).SubItems(1).Text, _
                              "mm/dd/yyyy", My.Computer.Info.InstalledUICulture)
    Dim Sql = "select * from [Student_Outcomes] where [OutcomeDate]  = #" & _
              datesel.ToString("dd-MMM-yy") & "# And [StudentID] = " & TextBox7.Text

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
awolarczukAuthor Commented:
yep i am using vb.net, i am pull the data for a converion so it i will mar sure i store it correctly now just to  confirm is there ment to be a dim with the sql there and when i do put that code in it errors when it gets to the _
0
 
Wayne Taylor (webtubbs)Commented:
Yeah, sorry, it should be like this....
Private Sub ListView2_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView2.MouseClick
        Dim rs As New ADODB.Recordset
        Dim datesel As Date = Date.ParseExact(ListView2.SelectedItems.Item(0).SubItems(1).Text, _
                              "mm/dd/yyyy", My.Computer.Info.InstalledUICulture)
        Dim sql As String = "select * from [Student_Outcomes] where [OutcomeDate]  = #" & _
              datesel.ToString("dd-MMM-yy") & "# And [StudentID] = " & TextBox7.Text
        Debug.Print(sql)
        rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly)
        studentnotes.Text = ""
        If Not (rs.EOF And rs.BOF) Then
            If Not IsDBNull(rs.Fields("OutcomeNotes").Value) Then
                studentnotes.Text = rs.Fields("OutcomeNotes").Value
            End If
        End If
        rs.Close()
    End Sub

Open in new window

0
 
awolarczukAuthor Commented:
ok i have used the code you gave me and for some reason it is changing the date all togeather to another date i have checked the database and where it is getting the data, the date it should be using is 19/06/2007 this is the date in the listview box where it is getting the data (see attached file first image) look at the list view 2nd from the top
the out put i am getting is select * from [Student_Outcomes] where [OutcomeDate]  = #06-01-2007# And [StudentID] = 54

06-01-2007 as the date

some weird is going on here lol
outcome-screen.gif
0
 
awolarczukAuthor Commented:
it seems that the month is just staying as 01 and the day and year is changing fine
0
 
Wayne Taylor (webtubbs)Commented:
Bugger. It's not getting the month value (mm = minutes, MM = month). The date parsing should have looked like this....

    datesel = Date.ParseExact(ListView2.SelectedItems.Item(0).SubItems(1).Text, _
                              "MM/dd/yyyy", My.Computer.Info.InstalledUICulture)

Apologies.

Wayne
0
 
awolarczukAuthor Commented:
shouldnt it be dd/MM/YYY?? as that the format i want to use
0
 
awolarczukAuthor Commented:
mate all working now with a combo of what you gave me and a few other things thanks so much for your help

0
 
awolarczukAuthor Commented:
Great to work with thanks so much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.