Results 1 to 7 of 7
  1. #1
    taryn is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    17

    I need to convert a date result in a query to UK format date

    Hello



    I have the following query, which gets me the date I need:

    SELECT CAST(FldValue - 2 as smalldatetime) as Date FROM WflowFields WHERE WflowNr='286' AND WfPageNr='78' AND LineNr='93'

    With the above, the result I get is: 2019-12-31 00:00:00

    However, I need the date in the UK format: 31-12-2019

    I have tried incorporating the CONVERT function into my query, but I don't think I'm understanding how it works properly, and I am not getting the result I want.

    Can anyone help?

    Kind regards.

    Taryn

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    format([datefld],"dd-mm-yyyy")
    in the field line

    or set it in the FORMAT property of the query fld.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I don't think CAST works with Access so you must be doing it in SQL server. Have a look at this link:
    https://docs.microsoft.com/en-us/sql...l-server-ver15

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

  4. #4
    taryn is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    17
    Hello

    How would you incorporate the above syntax into my query?

    Kind regards.

    Taryn

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Are you running it in SSMS or is this a pass-through query?

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

  6. #6
    taryn is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    17
    Quote Originally Posted by Gicu View Post
    Are you running it in SSMS or is this a pass-through query?

    Cheers,
    Vlad
    Thanks for your help. I have now solved my problem.

    Kind regards.

    Taryn

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by taryn View Post
    Thanks for your help. I have now solved my problem.

    Kind regards.

    Taryn
    Perhaps you'd be good enough to share your solution with us for the benefit of other members
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. Replies: 6
    Last Post: 02-01-2016, 01:57 PM
  2. convert result of date claculation to number
    By survivo01 in forum Queries
    Replies: 1
    Last Post: 08-12-2013, 02:46 PM
  3. Replies: 4
    Last Post: 03-12-2013, 06:49 PM
  4. Convert date format yyyymmdd for datediff
    By TEN in forum Programming
    Replies: 1
    Last Post: 06-17-2009, 09:35 PM
  5. Replies: 1
    Last Post: 06-11-2009, 05:40 PM

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