Results 1 to 6 of 6

Thread: DATE Conversion

  1. #1
    Join Date
    Jul 2002
    Posts
    42

    Exclamation Unanswered: DATE Conversion

    Hello,

    I want to take a date/time and convert it to a string of a specific format, like this :

    2002-11-18 14:51:30 to '20021118145130'

    Since we both work on Oracle and SQL Server, I did this in Oracle :
    SELECT to_char(date_field, 'YYYYMMDDHH24MISS') FROM table.

    There's got to be a way to do that as simply with SQL Server...

    Thanks in advance for the hand.

    SvRider.

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Lightbulb Re: DATE Conversion

    Using Sql Server 2000, I have typically implemented functions that collect and concatenate each portion of the date time (as multiple short varchar strings, appropriatly using '0' placeholders to "pad out" the resulting datetime string correctly).

  3. #3
    Join Date
    Nov 2002
    Location
    Italy
    Posts
    3

    Smile

    Try this:
    (convert(char(17),(convert(char(8),getdate(),112) + left(convert(char(12),getdate(),114),2) + substring(convert(char(12),getdate(),114),4,2) + substring(convert(char(12),getdate(),114),7,2) + right(convert(char(12),getdate(),114),3)))).
    It's my default value for a varcharfield called timestamp...

  4. #4
    Join Date
    Jul 2002
    Posts
    42
    Originally posted by lconsonni
    Try this:
    (convert(char(17),(convert(char(8),getdate(),112) + left(convert(char(12),getdate(),114),2) + substring(convert(char(12),getdate(),114),4,2) + substring(convert(char(12),getdate(),114),7,2) + right(convert(char(12),getdate(),114),3)))).
    It's my default value for a varcharfield called timestamp...

    Thanks lconsonni

    I came up with this :

    CONVERT(CHAR(8),DM_MAJ,112) + LEFT(CONVERT(CHAR(8),DM_MAJ,114),2) + SUBSTRING(CONVERT(CHAR(8), DM_MAJ, 114), 4, 2) + SUBSTRING(CONVERT(CHAR(8), DM_MAJ, 114), 7, 2)

    I just thought there would be a more "clean" way to do this. But anyways it works.

    Thanks again.

  5. #5
    Join Date
    Nov 2002
    Posts
    14

    Re: DATE Conversion

    What you need is to convert the date to ISO format:

    CONVERT( DATETIME, your_expression, 112 )
    Zlatko Michailov
    Z-SQL

  6. #6
    Join Date
    Nov 2002
    Location
    Italy
    Posts
    3
    Give me an example, please...

Posting Permissions

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