Converting Text in a Database to Date/Time fromat

[july07]

Hello, i am new to programming and VB.net any help will be appreciated.

I have a SQL database that i do not have a "write-access" to, the database has 2 columns that looks like this:

Date Time
20111025 0025

How do i create a new Column with VB, that will look like this:

Date/Time
10/25/2011 00:25


I could do this in MS Access with:

Date: CDate(mid([Date],5,2) & "/" & Mid([Date],7,2) & "/" & Left([Date],4))

Time: (Left([Time],2) & ":" & Mid([Time],3,2))

DateTime: [Date]&" "&[Time] 24 Hours

How do i do the same thing in VB.net, i would eventually want to use this Date/Time to calculate Distance.

Thanks,
July

 

[july07]

I just figured it out, i inserted it in my query!

I should have thought about it before asking the question!

Thanks,
July

 

[july07]

Im sorry, but the query did not work.

I tried to create a new column by adding the the original date and time and seperating them to the Date format, but it did not work.

My Query was:

Select Date, Time, (mid([Date],5,2) & "/" & Mid([Date],7,2) & "/" & Left([Date],4)) & " " & (Left([Time],2) & ":" & Mid([Time],3,2)) AS DateTime, From Table.

Can somebody help me pls, i think i would prefer converting it to date format in the code. I would want to do it for each rows in the columns.

Thanks in advance and i hope i was able to explain what i am trying to accomplish well.

 

[davejam]

you could try a convert on that

Select Date, Time,
convert(datetime,(mid([Date],5,2) & "/" & Mid([Date],7,2) & "/" & Left([Date],4)) & " " &  (Left([Time],2) & ":" & Mid([Time],3,2)),131) AS DateTime
From Table

convert code 131 formats to dd/mm/yy hh:mi:ss:mmmAM so hopefully should not give a problem!!!

You could also look into the function cast

daveJam

it works on my machine, so technically i win!

 

[Andrzejek]

Date and Time are terrible names for fields in the database - they are reserved words and should not be used.
I would convert them while I pull data from your database, something like:

Select [Date} As StartDate, [Time} As QuitTime ...

Have fun.

---- Andy

 

[july07]

Thanks Davejam,

The query still, does'nt work.

Thanks Andrzejek,

That is not the exact names of the fields in the database, i just used that as an example. I have tried different select query but still no success!

 

[gmmastros]

What are the data types for the columns. If you are not sure, run this:

Select * 
From   Information_Schema.Columns 
Where  Table_Name = 'YourTableNameHere' 
       And Column_Name In ('YourDateColumn','YourTimeColumn')

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[july07]

The data types in the source database is "text" and i am trying to convert it to a date format, from sql query.

For example the Column names are StartDate and StartTime has:
StartDate StartTime
20111025 0025

My query in VB is:

Dim sql as Date

 sql = "SELECT (Mid([StartDate],5,2) & " / " & Mid([StartDate],7,2) & " / " & Left([StartDate],4)) & " " & (Left([StartTime],2) & ":" & Mid([StartTime],3,2))  AS StartDateTime FROM BKTest"

The error im getting is
Conversion from string "SELECT (Mid([KilnPushDate],5,2) " to type 'Double' is not valid.

 

[davejam]

thinking about it, i don't think mid works with sql, think you have to use substring.

dim sql as date

sql = "select ...."

would bring up an error.

I would test the query directly against sql with the sql server management studio until you get what you need, then move it into your vb.net coding.



daveJam

it works on my machine, so technically i win!

 

[july07]

It works fine with "Sql Query" in Ms Access database.

I wonder if the syntax is different in VB.

 

[gmmastros]

If you are using a Microsoft SQL Server database, and you want to return a new column with the DateTime data type, you can do this (in the query).

Select Convert(DateTime, Convert(VarChar(10), StartDate) 
       + ' ' 
       + Stuff(Convert(VarChar(10),StartTime), 3,0,':')) As StartDateTime
From   YourTableName

Note that there is an extra step to convert from the text data type to the varchar data type. In your original post you said that you don't have write access to the database. This is a shame because it seems like an awful waste to use a text column to store just a couple characters of data.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

If you are using a Microsoft SQL Server database, and you want to return a new column with the DateTime data type, you can do this (in the query).

Select Convert(DateTime, Convert(VarChar(10), StartDate) 
       + ' ' 
       + Stuff(Convert(VarChar(10),StartTime), 3,0,':')) As StartDateTime
From   YourTableName

Note that there is an extra step to convert from the text data type to the varchar data type. In your original post you said that you don't have write access to the database. This is a shame because it seems like an awful waste to use a text column to store just a couple characters of data.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom