开发者

SQL Syntax for .dtq file query .dbf

Using the latest Visual Fox Pro OLE DB Provider inside Microsoft Visual Studio 2008 Pro Edition. I cannot use SQL functions inside my *.dtq files. I have over 181 *.dtq fi开发者_开发百科les that I ultimatley need to call from a sqlscript but my problem lies when attempting to parse a column in a dbf file. The column that I need contains a time ie. HH:MM:SS I need to be able to parse the time column to pull just the hour or minutes. The minutes will be sectioned in 15 minute intervalls so ie for 15 on the hour I can pull as long as time is > than 0 but < 15 so that I can pull data for 15 minute intervals. Usually I would use a function like parse or parsname or split or decimal or decim or something that says go to character space # and then # character spaces after that. Like in dos i can use %TIME:~3,2% to pull information I need for the minute section of the column that contains the time inforamtion that I need. Please help me. Thank you in advanced.


Although I'm not directly familiar with .dtq files (unless they are just .dbf files renamed), VFP has date/time functions to get different components of a date field for you. However, there is no data type for just "TIME" component. Its typically just stored as a string (character) column. However, if the column IS of a date/time, you can use

HOUR( YourDateTimeColumn ) -- will return based on 24 hour clock... 1pm = 13, 2pm = 14, etc
MINUTE( YourDateTimeColumn )

However, your example shows HH:MM:SS, so I'm going with stored as just a character string. So I would get it by...

select 
      val( left( YourTimeColumn, 2 )) as MyHour,
      val( substr( YourTimeColumn, 4, 2 )) as MyMinutes,
      int( val( substr( YourTimeColumn, 4, 2 )) / 15 ) as QtrMinute
   from
      YourTable

Slightly off of your > 0 and < 15 which doesn't represent a true 15 minutes, but 14,

0 - 14 minutes = 1st quarter minute
15- 29 minutes = 2nd quarter minute
30- 44 minutes = 3rd quarter minute
45- 59 minutes = 4th quarter minute

If you want the quarters based on 1-15 minutes, 16-30, 31-45, 46-60, then change to int( 1+val (...) as QtrMinute

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜