SQL Date string to Crystal 8 3

[jmr9999]

I am having difficulty manipulating a date string pulling off a SQL database. The format is YYYY/MM/DD HH:MM:SS.SS. Crystal is seeing it as a string, not DATETIME, I guess because of the SS.SS. Is there a way I could truncate the DATETIME coming from SQL into my CR report?

 

[sbdt]

Try making a new formula

Date(mid(sqldate, 1,4),mid(sqldate,6,2),mid(sqldate,9,2))

Where sqldate is the date you pull from your sql database

 

[jmr9999]

I am getting a "Too many arguments have been given to this function" when I try it. Any suggestions?

 

[jmr9999]

I forgot to mention, when I run a ISDATETIME function against the datetime field, I get a "FALSE."

 

[Apollo6]

jmr9999:

I was having the exact same issue, actually posted my question right after yours. This is what I figured out and it worked for my conversion:

DateTime(
ToNumber( Mid( {Your_Date_Field}, 1,4) ),
ToNumber( Mid( {Your_Date_Field}, 6,2) ),
ToNumber( Mid( {Your_Date_Field}, 9,2) ),
ToNumber( Mid( {Your_Date_Field}, 12,2) ),
ToNumber( Mid( {Your_Date_Field}, 15,2) ),
0 );

Hope it helps!

 

[LV]

You can also try this:
cdatetime(left({table.dateField}, instr("2000/01/01 11:39:15.15", ".&quot - 1))

 

[LV]

Sorry...It's been a long day.

cdatetime(left({table.dateField}, instr({table.dateField}, ".&quot - 1))

 

[synapsevampire]

The simplest is:

dtstodate("2002/12/31 12:34:43.32&quot

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[LV]

synapsevampire is absolutely right - that's the simpiest one. ...and if you need to display time:

dtstodatetime("2002/12/31 12:34:43.32&quot

 

[jmr9999]

Thanks for all the help...I tried it a couple of different ways, and it works great!