Date Output from SQL Server


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Date Output from SQL Server

  1. #1
    Bob Duffy Guest

    Date Output from SQL Server

    Hi,

    I have some ASP VbScript that loops through an SQL query and outputs the
    results to an HTML page. The problem is that date/time fields are coming out
    formatted as US format rather than UK format.

    Eg. "8/31/00 11:58:00 AM" instead of "31/08/00 11:58:00 AM"

    I have tried using the formatDateTime function of VBscript, but it still
    outputs as USA format.

    I have also checked the regional settings on server / client and they are
    dd/mm/yyyy


    Has anyone any suggestions ?

    Regards,


    Bob Duffy
    Prodata
    MCSD, MCDBA, MCSE

    Sample Code:
    ===========
    dim con
    dim rs
    dim strSQL

    set conn = server.CreateObject("ADODB.Connection")
    set rs = server.CreateObject("ADODB.Recordset")
    strSQL = "Select * from Blah"

    conn.Open ""DRIVER=SQL
    Server;SERVER=MyServer;APP=MyApp;WSID=BOB;DATABASE=MyDatabase;Trusted_Connec
    tion=Yes;Regional=Yes"

    rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly

    Response.Write "<TABLE>"
    do while not rs.EOF
    Response.Write "<TR>"
    for x = 0 to rs.Fields.Count-1
    Response.Write "<TD nowrap>"
    select case rs.Fields (X).type
    case 135 'Date Format
    Response.Write rs.Fields (x).Value
    case else
    Response.Write rs.Fields (x).Value
    end select
    Response.Write "</TD>"
    next
    Response.Write "</TR>"
    rs.MoveNext
    Loop
    Response.Write "</TABLE>"



  2. #2
    Sébastien Ros Guest

    Re: Date Output from SQL Server

    You have to change Regional Settings in the Configuration Panel.
    Then it will output the way you want

    ----
    Sébastien Ros
    s.ros@essaim.univ-mulhouse.Fr





  3. #3
    Bob Duffy Guest

    Re: Date Output from SQL Server

    Hi,

    I have checked the Regional Settings on the control panel and they are
    dd/mm/yy on both client and server.

    When I run queries in query analyser or any other App apart from IE it
    outputs in the correct format, (dd/mm/yy). Its just IE that doesnt give me
    the desired format.

    Any ideas ?

    Regards,


    Bob Duffy
    Prodata

    Sébastien Ros wrote in message <39b37741@news.devx.com>...
    >You have to change Regional Settings in the Configuration Panel.
    >Then it will output the way you want
    >
    >----
    >Sébastien Ros
    >s.ros@essaim.univ-mulhouse.Fr
    >
    >
    >
    >




  4. #4
    A D Kendall Guest

    Re: Date Output from SQL Server

    I think VBScript handles all dates internally in US format. (I'm sure I read
    it somewhere in MSDN)

    You could build up your output string using the Day(), Month() and Year()
    functions

    Bob Duffy <bob@prodata.ie> wrote in message news:39ae6828$1@news.devx.com...
    > Hi,
    >
    > I have some ASP VbScript that loops through an SQL query and outputs the
    > results to an HTML page. The problem is that date/time fields are coming

    out
    > formatted as US format rather than UK format.
    >
    > Eg. "8/31/00 11:58:00 AM" instead of "31/08/00 11:58:00 AM"
    >
    > I have tried using the formatDateTime function of VBscript, but it still
    > outputs as USA format.
    >
    > I have also checked the regional settings on server / client and they are
    > dd/mm/yyyy
    >
    >
    > Has anyone any suggestions ?
    >
    > Regards,
    >
    >
    > Bob Duffy
    > Prodata
    > MCSD, MCDBA, MCSE
    >
    > Sample Code:
    > ===========
    > dim con
    > dim rs
    > dim strSQL
    >
    > set conn = server.CreateObject("ADODB.Connection")
    > set rs = server.CreateObject("ADODB.Recordset")
    > strSQL = "Select * from Blah"
    >
    > conn.Open ""DRIVER=SQL
    >

    Server;SERVER=MyServer;APP=MyApp;WSID=BOB;DATABASE=MyDatabase;Trusted_Connec
    > tion=Yes;Regional=Yes"
    >
    > rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly
    >
    > Response.Write "<TABLE>"
    > do while not rs.EOF
    > Response.Write "<TR>"
    > for x = 0 to rs.Fields.Count-1
    > Response.Write "<TD nowrap>"
    > select case rs.Fields (X).type
    > case 135 'Date Format
    > Response.Write rs.Fields (x).Value
    > case else
    > Response.Write rs.Fields (x).Value
    > end select
    > Response.Write "</TD>"
    > next
    > Response.Write "</TR>"
    > rs.MoveNext
    > Loop
    > Response.Write "</TABLE>"
    >
    >




  5. #5
    Bob Duffy Guest

    Re: Date Output from SQL Server

    Hi,

    You must be right. I've opted for using the Day, Month, Year route and it
    works - a bit slower I'm sure but at least its works ;-)

    Many Thanks,


    Bob Duffy
    Prodata




  6. #6
    Gene Black Guest

    Re: Date Output from SQL Server

    See the documentation for the T-SQL function 'convert'

    you should be able to do the following

    select convert(char,mydate,3) from mytable

    or

    select convert(char,mydate,103) from mytable

    This will return a date in the format

    dd/mm/yy
    dd/mm/yyyy

    respectively

    If you need the time also add a '+ convert(char,mydate,8)'



    "Bob Duffy" <bob@prodata.ie> wrote in message
    news:39ae6828$1@news.devx.com...
    > Hi,
    >
    > I have some ASP VbScript that loops through an SQL query and outputs the
    > results to an HTML page. The problem is that date/time fields are coming

    out
    > formatted as US format rather than UK format.
    >
    > Eg. "8/31/00 11:58:00 AM" instead of "31/08/00 11:58:00 AM"
    >
    > I have tried using the formatDateTime function of VBscript, but it still
    > outputs as USA format.
    >
    > I have also checked the regional settings on server / client and they are
    > dd/mm/yyyy
    >
    >
    > Has anyone any suggestions ?
    >
    > Regards,
    >
    >
    > Bob Duffy
    > Prodata
    > MCSD, MCDBA, MCSE
    >
    > Sample Code:
    > ===========
    > dim con
    > dim rs
    > dim strSQL
    >
    > set conn = server.CreateObject("ADODB.Connection")
    > set rs = server.CreateObject("ADODB.Recordset")
    > strSQL = "Select * from Blah"
    >
    > conn.Open ""DRIVER=SQL
    >

    Server;SERVER=MyServer;APP=MyApp;WSID=BOB;DATABASE=MyDatabase;Trusted_Connec
    > tion=Yes;Regional=Yes"
    >
    > rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly
    >
    > Response.Write "<TABLE>"
    > do while not rs.EOF
    > Response.Write "<TR>"
    > for x = 0 to rs.Fields.Count-1
    > Response.Write "<TD nowrap>"
    > select case rs.Fields (X).type
    > case 135 'Date Format
    > Response.Write rs.Fields (x).Value
    > case else
    > Response.Write rs.Fields (x).Value
    > end select
    > Response.Write "</TD>"
    > next
    > Response.Write "</TR>"
    > rs.MoveNext
    > Loop
    > Response.Write "</TABLE>"
    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center