Fixing the "date"

I am using MS SQL SERVER 2008 R2 database from my VB.NET program.

I want to find a way to fix my problem. My problem is that some of the records(date) in the files that I imported from textfile are like this "105" where it should be "000105(00-01-05)".What I did to fix the prob for the mean time is that instead of "date", I assigned them as "int". But now, it needs to be in "date" format. Im having problem with those "Zeros". Is there any way where I can import the records as date(yy-mm-dd) and not int? I also tried to change and assign them as date in MS SQL SERVER 2008 R2 but im getting an error. I think the problem is that it cant be recognized as date because some records consist 3-5 digits only, where it should be 6 digits for them to be recognized as date (yymmdd)
Attached is a sample screenshot of sample record.

Here's my code of migration:

Code:

---

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim files As ReadOnlyCollection(Of String)
        files = My.Computer.FileSystem.GetFiles("C:\TEXTFILE", FileIO.SearchOption.SearchAllSubDirectories, "*.txt")
        For Each a As String In files
            Dim filename As String = a
            Dim fields As String()
            Dim delimiter As String = "|"
            Dim connectionString As String

            Using parser As New TextFieldParser(filename)
                parser.SetDelimiters(delimiter)
                While Not parser.EndOfData
                    ' Read in the fields for the current line
                    fields = parser.ReadFields()
                    ' Add code here to use data in fields variable.

                    'INSERT STATEMENT HERE
                    connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=****;Integrated Security=True;User Instance=True"
                    Using connection As New SqlConnection(connectionString)


                        'Dim insert As New SqlCommand("INSERT INTO Certmast (CER_HOLD_CODE,CER_CERT_CODE, CER_CERT_ISSUE_DATE, CER_NOSHARES, CER_REASON_CODE, CER_CUR_STATUS, CER_PREV_STATUS, CER_ORIGIN_STK_CODE, CER_ORIGIN_CERT_CODE, CER_ORIGIN_REASON_CODE, CER_LOSS_PUB_DATE, CER_UPDATE_DATE, CER_USER_ID) " _
                        '                                            & "VALUES (@CER_HOLD_CODE,@CER_CERT_CODE, @CER_CERT_ISSUE_DATE, @CER_NOSHARES, @CER_REASON_CODE, @CER_CUR_STATUS, @CER_PREV_STATUS, @CER_ORIGIN_STK_CODE, @CER_ORIGIN_CERT_CODE, @CER_ORIGIN_REASON_CODE, @CER_LOSS_PUB_DATE, @CER_UPDATE_DATE, @CER_USER_ID)", _
                        '        connection)



                        'insert.Parameters.AddWithValue("@CER_HOLD_CODE", fields(0).Trim.ToString)
                        'insert.Parameters.AddWithValue("@CER_CERT_CODE", fields(1).Trim.ToString)
                        'insert.Parameters.AddWithValue("@CER_CERT_ISSUE_DATE", fields(2).Trim.ToString)
                        'insert.Parameters.AddWithValue("@CER_NOSHARES", fields(3).Trim.ToString)
                        'insert.Parameters.AddWithValue("@CER_REASON_CODE", fields(4).Trim.ToString)
                        'insert.Parameters.AddWithValue("@CER_CUR_STATUS", fields(5).Trim.ToString)
                        'insert.Parameters.AddWithValue("@CER_PREV_STATUS", fields(6).Trim.ToString)
                        'insert.Parameters.AddWithValue("@CER_ORIGIN_STK_CODE", fields(7).Trim.ToString)
                        'insert.Parameters.AddWithValue("@CER_ORIGIN_CERT_CODE", fields(8).Trim.ToString)
                        'insert.Parameters.AddWithValue("@CER_ORIGIN_REASON_CODE", fields(9).Trim.ToString)
                        'insert.Parameters.AddWithValue("@CER_LOSS_PUB_DATE", fields(10).Trim.ToString)
                        'insert.Parameters.AddWithValue("@CER_UPDATE_DATE", fields(11).Trim.ToString)
                        'insert.Parameters.AddWithValue("@CER_USER_ID", fields(12).Trim.ToString)

                        'insert.Connection.Open()
                        'insert.ExecuteNonQuery()
                        'insert.Connection.Close()

---

Try removing the .ToString or placing it outside of the brackets. Do the other date field display correctly? What is the data type for that particular field?

Quote:

---

Originally Posted by Nightwalker83

Try removing the .ToString or placing it outside of the brackets. Do the other date field display correctly? What is the data type for that particular field?

---

Still not working. I assigned them as INT. The other columns are working properly.

Why int when you are trying to insert a date?

Quote:

---

Originally Posted by Nightwalker83

Why int when you are trying to insert a date?

---

Try to look at the attached images :( Those are my problem

For example:

There is a:

"105" where it should "00-01-05 (2000-01-05)"

The problem is that the records contains 3,4 and 5 digits, that's why the server cant recognize them as "date". This is why I decided to assigned them as "int" for the sake of going forward on the development. But now, i need to assign them as date :(

How will I add " 0(zero) " beside those 3,4 and 5 digits for the server/database recognize them as date?

I TRIED THIS CODE AND THE RESULT IS EXACTLY WHAT I WANTED TO HAPPEN. BUT HOW WILL I APPLY THIS ON MY DATABASE? THIS IS ONLY SELECT. AM I GOING TO DO SOMETHING UPDATE? CREATE ? :)

Code:

---

SELECT TOP 17 RIGHT ('000000'
+ CAST (CER_CERT_ISSUE_DATE AS VARCHAR (6)), 6) CER_CERT_ISSUE_DATE
  FROM [sto_database].[dbo].[Certmast]
  order by CER_CERT_ISSUE_DATE asc
 


SELECT TOP 17 RIGHT (REPLICATE('0', 6)
+ CAST (CER_CERT_ISSUE_DATE AS VARCHAR (6)), 6) CER_CERT_ISSUE_DATE
  FROM [sto_database].[dbo].[Certmast]
  order by CER_CERT_ISSUE_DATE asc

---

See if this idea works for you - add an expression field to your database...

something like this:

Code:

---

ALTER TABLE [sto_database].[dbo].[Certmast]
ADD NEW_CERT_ISSUE_DATE as RIGHT(REPLICATE('0', 6) + CONVERT(varchar(6), CER_CERT_ISSUE_DATE), 6)
go

---

now when you do a select from your table, you'll have a new field ... keep in mind, it's read-only though... and its value is driven by the CR_CERT_ISSUE_DATE field, so any updates would need to go against that field.

-tg

Quote:

---

Originally Posted by techgnome

See if this idea works for you - add an expression field to your database...

something like this:

Code:

---

ALTER TABLE [sto_database].[dbo].[Certmast]
ADD NEW_CERT_ISSUE_DATE as RIGHT(REPLICATE('0', 6) + CONVERT(varchar(6), CER_CERT_ISSUE_DATE), 6)
go

---

now when you do a select from your table, you'll have a new field ... keep in mind, it's read-only though... and its value is driven by the CR_CERT_ISSUE_DATE field, so any updates would need to go against that field.

-tg

---

Thanks! This works! I can see that it can be converted as varchar but not date :( but its ok for the mean time. Can I insert record in this new column?

of course you can't convert it to a date... 111020 isn't a valid date... it's just a number...

and what do you mean by "insert record in this new column?" ... it's read only... you can read the value, but you can't insert a value into it, if that's what you're asking.

-tg

Quote:

---

Originally Posted by techgnome

of course you can't convert it to a date... 111020 isn't a valid date... it's just a number...

and what do you mean by "insert record in this new column?" ... it's read only... you can read the value, but you can't insert a value into it, if that's what you're asking.

-tg

---

Can you suggest a way where I can get the values of CER_CERT_ISSUE_DATE (with the fix format of (example: "000105) instead of "105") where I can also insert new records? :( Do I need to create a query in sql server? Do I need to import the .txt files again but if I will be importing how will I import them as "000000"? :( Any suggestion?