String to date - Xtreme Visual Basic Talk

05-03-2012, 08:16 AM

AlexanderJames AlexanderJames is offline

Junior Contributor

Join Date: Sep 2005

Posts: 381

String to date

Hi All

Ive got the horrble task of interfacing to an application that stores it's date and time as a string with the database fields set to char.I have written the below query and i dont get any errors but i also dont get any data back.

Code:

Dim DateToday As Date
    DateToday = Format(Now, "dd/mm/yyyy")
    
    gstrSQL = "SELECT CONVERT(datetime,C_Date,103) as NewDate, C_Time, L_UID, C_Name FROM tEnter WHERE C_Date = '" & DateToday & "'"

This is new territory for me so any assistance would be greatly appreciated.

Many Thanks
AJ

__________________
www.necsys-software.com

05-03-2012, 09:25 AM

TheRealTinTin TheRealTinTin is offline

Junior Contributor

Join Date: Nov 2008

Location: Glasgow, UK

Posts: 328

Try the following instead

Code:

gstrSQL = "SELECT CONVERT(datetime,C_Date,103) as NewDate, C_Time, L_UID, C_Name FROM tEnter WHERE C_Date = #" & DateToday & "#;"

__________________
Artificial Intelligence is no match for natural stupidity

05-03-2012, 09:52 AM

AlexanderJames AlexanderJames is offline

Junior Contributor

Join Date: Sep 2005

Posts: 381

Hi TherealTinTin

Thanks for your reply.
I tried your plan but i get no data back either, i have no idea why this company stores it's date as a string, makes no sence and makes it difficult.

__________________
www.necsys-software.com

05-03-2012, 12:32 PM

loquin

Google Hound

Retired Moderator
* Guru *

Join Date: Nov 2001

Location: Arizona, USA

Posts: 12,378

Which database/version are you using? It looks like SQL server (from your code, Alexander,) but, which version?

You may want to apply the convert in the where clause as well, rather than convert the current date to a string for the comparison.

i.e.

Code:

"... WHERE DATEPART(CONVERT(datetime,C_Date,103)) = DATEPART(GETDATE())"

__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by loquin; 05-03-2012 at 12:41 PM.

05-04-2012, 09:39 AM

AlexanderJames AlexanderJames is offline

Junior Contributor

Join Date: Sep 2005

Posts: 381

Hi Loquin

Thanks for your reply, I am using sql express 2008. I managed to get that problem solved by declaring my date as a string then formatting it "yyyymmdd" as this is how it appears in the database. These guys also store the time in a field called C_Time also as a string and i simply cannot get this time out. I have the following code.

Code:

Dim DateToday As String
    DateToday = Format(Date, "yyyymmdd")
    ActiveReadUnis "SELECT CONVERT(datetime,C_Date,103) as NewDate, CONVERT(Char,C_Time,108) as NewTime , L_UID, C_Name FROM tEnter WHERE C_Date = '" & DateToday & "' and C_Name <> '" & " " & "'"

C_Time always comes out as 00:00:00 no matter what i do, the above convert statement should do the trick but no luck. Any ideas ?

Thanks
AJ

__________________
www.necsys-software.com

05-10-2012, 02:53 PM

loquin

Google Hound

Retired Moderator
* Guru *

Join Date: Nov 2001

Location: Arizona, USA

Posts: 12,378

Could you copy/paste a few examples of the time data? (include times with hours in the range of 0 through 9, hours in the range of 10-12, minutes in the range of 0-9 , and minutes in the range of 10-59. And, if seconds are included, with seconds in the range 0-9 and seconds in the range of 10-59.)