Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    26

    Question Unanswered: Oracle To SQLServer conversion for SELECT TO_CHAR(SYSDATE,'DDMMYYYYHHMMSS')

    Hi,

    i am trying to convert the following oracle sql,

    SELECT TO_CHAR(SYSDATE,'DDMMYYYYHHMMSS')
    (oraclequery)
    into a MSSQLServer sql query.

    Could you help me to get the equivalent of the above query in sqlserver.

    PS: i tried using the following query, but i cannot get the month equivalent as 01/02/03/04 instead i get january/febrauary/march, etc

    SELECT
    (DATENAME(d, GETDATE())+
    DATENAME(m, GETDATE())+
    DATENAME(yyyy, GETDATE())+
    DATENAME(hh, GETDATE())+
    DATENAME(mi, GETDATE())+
    DATENAME(ss, GETDATE()))
    AS "Month Name"

    (ms sqlserver)
    The above query gives output as "6November2004174837"

    what i need as result is "06112004174837"

    Thanks,
    Gopi.
    Follow your DREAMS...
    Last edited by gopidba; 11-06-04 at 08:26. Reason: email notification

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select replace(convert(char(10),getdate(),104),'.','')
    +replace(convert(char(8),getdate(),14),':','')
    Last edited by r937; 11-06-04 at 09:22.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    26

    Thank you

    Thank you SQL Consultant. The following query also works.

    SELECT
    CASE
    WHEN LEN(DATENAME(d, GETDATE())) = 1
    THEN '0'+DATENAME(d, GETDATE())
    ELSE
    DATENAME(d, GETDATE()) END+
    CAST(MONTH(GETDATE()) AS VARCHAR)+
    DATENAME(yyyy, GETDATE())+
    DATENAME(hh, GETDATE())+
    DATENAME(mi, GETDATE())+
    DATENAME(ss, GETDATE())
    AS "Month Name"

    Thanks,
    Gopi.
    Follow your DREAMS...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, Registered User, that will not work in january through september
    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
  •