Results 1 to 9 of 9
  1. #1
    ry94080 is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2019
    Posts
    29

    SQL Statement to change time from 0734 to 07:34

    Hello,



    I have a time field that is formatted like 0734, i'm trying to format it as 07:34 with the statement below:

    SUBSTRING(ResultTime,1,2) + ':' + SUBSTRING(ResultTime,3,2) AS Result_time

    But, it's not working? What am i doing wrong? Any suggestions?

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you tried using the Format() function:
    Format([ResultTime],"Short Time")
    https://support.microsoft.com/en-us/...5-ba24d881b698
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    ry94080 is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2019
    Posts
    29
    I'm actually writing the SQL statement as a Pass-through query in Access. So i believe that wouldn't work?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're right, it won't. What exactly is happening with your initial expression, are you getting an error or an unexpected result?
    EDIT: looks like Right() and Left() work in SQL Serve so maybe try:
    Left([ResultTime],2) + ':' + Right([ResultTime],2) AS Result_time

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    ry94080 is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2019
    Posts
    29
    So with your example above, the query runs. But with a Resulttime of 1222, it is returning 34? That makes no sense?

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    It kinda' does as it treats the two pieces as numbers and adds them up....So use Concat() instead:

    Concat(Left([ResultTime],2),':' Right([ResultTime],2)) AS Result_time

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Why would it add if there is colon between them?

    Concatenation with + operator works for me in pass-through query.

    So does Format() function.
    Last edited by June7; 05-03-2023 at 10:16 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by June7 View Post
    Why would it add if there is colon between them?

    Concatenation with + operator works for me in pass-through query.
    OP omitted the : ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    if [Resulttime] is a datetime field :

    select format([Resultime],'mm:ss')

    or

    cast(datepart(minute, [Resultime]) as varchar(2)) + ':' + cast(datepart(second, [Resultime]) as varchar(2))

    for varchar or nvarchar fields the substring function should work

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 04-06-2022, 02:02 PM
  2. Replies: 3
    Last Post: 04-03-2021, 01:01 PM
  3. Replies: 3
    Last Post: 01-01-2014, 11:21 PM
  4. Replies: 13
    Last Post: 12-10-2012, 03:30 PM
  5. If Statement, Need to change value to 0
    By burrina in forum Access
    Replies: 5
    Last Post: 11-02-2012, 08:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums