locked
Working with Dates and Times RRS feed

  • Question

  • Hi,

    I have a SQL statement that returns a date and a time, from a datetime field - using the following lines:

    select
    (convert(varchar30,field_name,107)) as date,
    (convert(varchar30,field_name,108)) as time
    from My_Table

    This gives me, for example, Feb 26 2010 08:00:00

    Is there any way that I can easily drop the final 00's from the result?

    Simple question I know, but by looking at all of the date and time combinations, I cannot work it out! Unless I am being a noobie!

    Thanks
    Matt
    Friday, February 26, 2010 7:53 AM

Answers

  • Try this....
    select

    (

     

    convert(varchar(30),@getdate,107)) as date,

    --(convert(varchar(30),@getdate,108)) as time

    LEFT(

     

    convert(varchar(30),@getdate,8),5) as time

    Friday, February 26, 2010 8:13 AM

All replies

  • There is not a format for exactly what you require, try

    select
    (convert(varchar(30),field_name,107)) as date,
    (convert(varchar(5),field_name,108)) as time
    from My_Table
    Notice how ive used a varchar(5) to truncate the time portion


    Dave Ballantyne ---- http://sqlandthelike.blogspot.com
    Friday, February 26, 2010 8:06 AM
  • Try this....
    select

    (

     

    convert(varchar(30),@getdate,107)) as date,

    --(convert(varchar(30),@getdate,108)) as time

    LEFT(

     

    convert(varchar(30),@getdate,8),5) as time

    Friday, February 26, 2010 8:13 AM