Solved

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

Posted on 2008-06-22
11
2,073 Views
Last Modified: 2011-10-19
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

0
Comment
Question by:awolarczuk
  • 6
  • 4
11 Comments
 
LVL 6

Expert Comment

by:kennethfine
ID: 21840535
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 21840587
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
 

Author Comment

by:awolarczuk
ID: 21843612
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 21843792
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
 

Author Comment

by:awolarczuk
ID: 21861714
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:awolarczuk
ID: 21861725
it seems that the month is just staying as 01 and the day and year is changing fine
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 21861845
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
 

Author Comment

by:awolarczuk
ID: 21861874
shouldnt it be dd/MM/YYY?? as that the format i want to use
0
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 21861987
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
 

Author Comment

by:awolarczuk
ID: 21862150
mate all working now with a combo of what you gave me and a few other things thanks so much for your help

0
 

Author Closing Comment

by:awolarczuk
ID: 31469526
Great to work with thanks so much
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now