Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
How to store time (not date) in SQL and query from VFP
Message
From
28/02/2008 20:19:37
John Ryan
Captain-Cooker Appreciation Society
Taumata Whakatangi ..., New Zealand
 
 
To
28/02/2008 20:02:10
John Ryan
Captain-Cooker Appreciation Society
Taumata Whakatangi ..., New Zealand
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Environment versions
SQL Server:
SQL Server 2005
Miscellaneous
Thread ID:
01297626
Message ID:
01297632
Views:
37
Tracy, if you can wait for MSSQL2008, there is a time field type. ;-)

Assuming you need to move ahead now: if you're absolutely sure you don't need the date(!) then storing the time as a char field has advantages- not least that it allows blank values as per a VFP table, which a MSSQL date or datetime field does not.

Assuming you use a char field: If the user is selecting from an array/drop-down then it's easy to populate the field, using 24hr time of course.

You could include the : values as per your TIME() suggestion, or rely on chrtran(time(),[:],[]) or SUBSTR(TTOC(DATETIME(),1),9) if you're feeling concise. ;-)

The SQL is easy:
lcTime=chrtran(time(),[:],[])
lcMySQL=[select * from dbo.mytable where cTime<=?lctime]
...
...
...
Note that if you are allowing blank time values, the blanks will be pulled by the above SQL. If this matters you might *want* to use a NULL value so that blank entries aren't caught.

FWIW the above parameterized query will be cached by SQL Server, giving performance advantages over concatenated SQL. It is also injection-proof. ;-)

Finally, you might also consider using an integer field if you want to store the whole time value without wasting bytes, but the cost of conversions etc probably exceeds the cost of a few bytes per record. ;-)
"... They ne'er cared for us
yet: suffer us to famish, and their store-houses
crammed with grain; make edicts for usury, to
support usurers; repeal daily any wholesome act
established against the rich, and provide more
piercing statutes daily, to chain up and restrain
the poor. If the wars eat us not up, they will; and
there's all the love they bear us.
"
-- Shakespeare: Coriolanus, Act 1, scene 1
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform