SQL Functions Sintax

[Simbani]

LabVIEW 8.5 and Database Connectivity Toolkit and Microsoft Jet 4.0 OLE DB Provider

I have built the DB structure using the MS ACCESS 2003. One of it´s fileds, I have defined as Short Date, as I had beleived that I was going to get just the date, for instance, 06/07/2007, but instead of this I get the day, 06/07/2007 plus the time, 12:24:34, all together 06/07/2007 12:24:34

My two questions :

1) how to store in the date base, just the date, no the time

2) the SQL Function syntax to display just the date. I thought the correct sintax was : Select SUBSTR(xxx,0,10) from Table. Where xxx is the name of the field in the Table, 0 is the first character of the string xxx and it´s length

Thanks

Simbani

[AitziberH]

Hi,

if you only want the date instead date+time, then you could use the function called Get Date/Time String.vi in the Timing Palette, instead of DB Tools Format Datetime Str.vi, because the former allows you getting date and time individually.

And if you doubt about the SQL commands to query a substring, you could use the same functionality in LabVIEW, I mean, you could use the String Subset.vi in String Palette.

[smercurio_fc]

To answer your direct questions:

1) The "Date/Time" data type is similar to the smalldatetime data type in SQL Server. This stores time information. Can't change this. If you only want to store date information then you need to make your column's datatype to be of character-type. I think "Text" is what Access 2003 uses. Of course, this means you lose anything related to dealing with that column as a date.

2) If Access 2003 follows the same syntax as SQL Server's T-SQL, the SUBSTR won't work with a column that is a date/time data type. That function would only work with strings. This means you have to first convert the column value to a string. I know that in SQL Server's T-SQL the CAST and CONVERT functions would be used. Have no idea what they would be in Access 2003, so you'll need to check the manual.