SQL help again - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL help again

Author

Topic

hek78
Starting Member

10 Posts

Posted - 2003-12-04 : 20:35:26

What is the expression to retreive this data using SQL?

Table Name: DateTable
Field Name: Time

1/Dec/2003:08:23:22
1/Dec/2003:08:24:23

I want to get the last 8 characters from this text string and convert it to a date/time format.

i.e. My results should be

08:23:22
08:24:23

in date/time format.

By the way, I need this SQL to retrieve some data from access database from a php web.

thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-04 : 20:40:35

SELECT CONVERT(CHAR(8), Time, 108)
FROM DateTable

Style 108 of the CONVERT statement retrieves only the time portion of a datetime column. Have a look at CONVERT in Books Online for other useful things that it can do with datetime columns using styles.

Tara

hek78
Starting Member

10 Posts

Posted - 2003-12-04 : 20:50:47

Hi thanks for the reply.

But 1/Dec/2003:08:24:23 that I have written down is actually a text formate instead of a Date/time format.

I am thinking of extracting the last 8 characters to get this text string 08:24:23
and then convert this to a time/format? is this possible?

By the way, I am not able to change 1/Dec/2003:08:24:23 to a date time format since its generated by someone else.

Thanks

hek78
Starting Member

10 Posts

Posted - 2003-12-04 : 21:36:25

By the way? anyone know I can use the function Substr

substr(time, -1, 8)

Got an error...
Undefined function 'substr' in expression.,

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-12-04 : 21:48:15

In SQL Server it's spelled SubString().

hek78
Starting Member

10 Posts

Posted - 2003-12-04 : 21:54:06

That doesn't work as well. Using php webpage to extract data from Access 97.
Anoher other suggestion that I can extract the lsat 8 characters of TEXT string 1/Dec/2003:08:23:22 ?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-12-04 : 21:58:00

Ooooooooooooops, it would help if I READ the forum title

. In Access you'd use the Mid() function.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-04 : 22:20:19

or the RIGHT() function if you want the last 8 chars... RIGHT(Expression,8)

- Jeff

hek78
Starting Member

10 Posts

Posted - 2003-12-04 : 22:20:27

Wooww.. That works perfectly. Thanks! Ü
Now another problem is, I need to convert
the text string 08:23:22, what I extracted to time format! so that I can do some calculation. What function should I use?
Access 97, SQL

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-05 : 03:24:55

Run this:

MsgBox CSng(CDate("08:30:00"))

MsgBox CDate(0.999)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources