Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    8

    Question Unanswered: Date format when submitting queries

    Hi, when submitting a query on a date field I need to query the date in american format (mm/dd/yy). When returning the date as part of another query it displays it in British format (dd/mm/yy) which is what I want. Regional settings etc are correct - what else should I be looking for?

    Thanks,
    Tim

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76
    the great things about dates is that you can put them and view them in all types of ways.

    If you want to be sure you are always in correct format just pick apart the date and using datepart. and you can grab the month, day, year, hour, minute and seconds and put them in any order. So have fun with it, dates just take a little time to get used to but they offer lots of help with date functions. DATEDIFF, DATEADD, DATEPART, DATENAME

    exELECT cast(DATEPART(day, GETDATE())as varchar(2))
    +'/'+
    cast(DATEPART(month, GETDATE())as varchar(2))
    +'/'+
    cast(DATEPART(year, GETDATE())as varchar(4))

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What interface are you using? Query analyzer? Access? VB?

    If you run the following code in Query Analyzer, does it display March 4 ro April 3?

    select Convert(varchar(20), cast('3/4/2003' as Datetime))

    blindman

  4. #4
    Join Date
    Oct 2003
    Posts
    83
    Just try this
    select convert( varchar(40),dateformat_field,101)

  5. #5
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    i think he wants to enter the date in british format while entering it in the query.

  6. #6
    Join Date
    Jun 2004
    Posts
    25
    that's my problem too, i can convert the date as dd/mm/yyyy format when display, but when input the data, users are used dd/mm/yyyy format too, so when data is saved, error message will pop up. How can I convert the dd/mm/yyyy format into the mm/dd/yyyy format. Thanks for the help!

  7. #7
    Join Date
    Jun 2004
    Posts
    25
    that's my problem too, i can convert the date as dd/mm/yyyy format when display, but when input the data, users are used dd/mm/yyyy format too, so when data is saved, error message will pop up. How can I convert the dd/mm/yyyy format into the mm/dd/yyyy format. Thanks for the help!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SET DATEFORMAT dmy

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •