Need convert date format in sql SELECT statement!

[sreeky]

I have a VB pgm and a sql query in that selects a date.
need to convert the date from YYYY-MM-DD to mm/dd/yy as the sequel server doesn't recognize the YYYY-MM-DD date format.

Any help

thanks,
sreeky

 

[Phi1Smith]

Have you tried

mydate = Format(myolddate,"mm/dd/yy&quot

 

[CajunCenturion]

As a last resort, you could always do the following:

NewDate = SubStr(OldDate, 6, 2) & "/" & _
SubStr(OldDate, 9, 2) & "/" & _
SubStr(OldDate, 3, 2)
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[sreeky]

Thanks for the info, but I need the function inside the SQL SELECT statement as the recordset is erring out when i tru to do
getTheDate = myRs("myDate&quot

thanks

 

[CajunCenturion]

what exactly is the error message that you're getting, and how is getTheDate defined?
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[sreeky]

i hv this sql
mscSQL = "select D_SYS_CLSD from P.PQNCQIR where I_QLTYISS = 123"

where actualCloseDateOut is declared as a string

whenever i try to return a date using this statement below
actualCloseDateOut = mscRs("D_SYS_CLSD&quot, it errors out.
format of D_SYS_CLSD in the db2 database is yyyy-mm-dd.

error =>
Multiple-step OLE DB operation generated errors. Check each OLE DB
status
value, if available. No work done.

please let me know if you require any other info....
thanks

 

[Kendel]

You need to declare actualCloseDateOut as Date.
I assume "D_SYS_CLSD" is a Date type as well.

 

[sreeky]

even if i declare that variable as date doesn't solve the problem.
tell me how should my sql look like if i declare that as date?
i did try the below sqls with string and with date, they didn't work.
mscSQL = "select DateValue('D_SYS_CLSD') from P.PQNCQIR where I_QLTYISS = 123"

AND

mscSQL = "select DatePart('YY/MM/DD',D_SYS_CLSD) from P.PQNCQIR where I_QLTYISS = 123"
I don't know whatelse to try?

 

[Kendel]

So, D_SYS_CLSD is already a date type in the format of yyyy-mm-dd.

No need to change the SQL statement, use the old one:
mscSQL = "select D_SYS_CLSD from P.PQNCQIR where I_QLTYISS = 123"

Dim actualCloseDateOut as Date

actualCloseDateOut = mscRs.fields("D_SYS_CLSD&quot
OR
actualCloseDateOut = mscRs.fields("D_SYS_CLSD&quot.Value

 

[AWithers]

does this help?

"select convert(varchar(10),D_SYS_CLSD,1) from P.PQNCQIR where I_QLTYISS = 123"

Andy

 

[sreeky]

no! it is in the format mm/dd/yy. That is the standard right when we declare it as Date in vb program.

now the date in db2 database is in YYYY-MM-DD, which it is not liking.

 

[sreeky]

what ever function i give inside the SELECT statement, I get this error =>
-2147467259,[IBM][CLI Driver][DB2] SQL0440N No Function by the name "CONVERT" having compatible arguments is found in the function path. Sqlstate = 42884

This is when i try using convert function. same with the other functions too.

 

[Kendel]

After mscSQL = "select D_SYS_CLSD from P.PQNCQIR where I_QLTYISS = 123"

Debug.Print mscSQL.fields("D_SYS_CLSD&quot

and se what you got.