Fixing the "date"

[anamada]

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:

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()

[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?

[anamada]

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.

[Nightwalker83]

Why int when you are trying to insert a date?

[anamada]

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?

[anamada]

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 ?

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

[techgnome]

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

something like this:

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

[anamada]

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

something like this:

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?

[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

[anamada]

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?