FreeTDS Development Group

["James K. Lowden" <jklowden AT speakeasy.org>]

James Cameron wrote:
> 
> James,
> 
> While you've got the whole date thing in pieces on your workbench, any
> idea how I can get a datetime down to the seconds, or whatever precision
> it is being stored at?

Hi James, 

The standard answer is to use Convert.  I think you want style 109,
which yields "mon dd yyyy hh:mm:sss AM".  If you don't want
milliseconds, you could concatenate styles 106 & 108.

> 
> If I do a
> 
>         SELECT modify_stmp FROM table [...]
> 
> then I get
> 
>         'Feb 20 2001 10:57AM'

and if you do a
          SELECT convert( varchar(30), modify_stmp, 109 ) 
you'd see
          'Feb 20 2001 10:57:304 AM'

or something like that.

Precision:  smalldatetime has minutes only, no seconds.  datetime is
good to 300 milliseconds.  

The dblib function dbdatecrack will break up a datetime into a DBDATEREC
structure.  That'll let you get to the constituent parts within the
limitations of the C runtime library.  

If you want to do something completely different, you *could* interpret
the datetime value yourself by converting it to a binary datatype
first.  I have code that breaks a native datetime column structure (two
4-byte integers) into a struct tm, if you want to go there.  

--jkl