Page 1 of 6 123 ... LastLast
Results 1 to 15 of 81

Thread: Dates

  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Dates

    I have a field where the date is in a number string, for example, 20020731. I need to convert this into a date string. Any ideas?
    Thanks.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Look up convert in the Holy Book (SQL Server Books Online)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Dates

    Several possibilities:
    1. Write a scalar UDF that returns a date time from a set string format.

    2. USe the following T-SQL (though it may be slow):
    Code:
    declare @DateString varchar(8)
    
    select @DateString = '20030731'
    
    select
    	cast(substring(@DateString, 5, 2) + '/' + substring(@DateString, 7, 2) + '/' + substring(@DateString, 1, 4) as DateTime)
    3. If you are importing this date into your database from another data source using DTS, you can use on of the Copy options to specify that the source is a date/time string (and then specify the precise format).

    Regards,

    Hugh Scott
    Originally posted by exdter
    I have a field where the date is in a number string, for example, 20020731. I need to convert this into a date string. Any ideas?
    Thanks.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Usually, CONVERT is used to transform a date/time into a char or varchar data type. Looking at it, I don't see anything that would immediately allow you to take a string an convert it to date/time.

    Regards,

    hmscott


    Originally posted by Enigma
    Look up convert in the Holy Book (SQL Server Books Online)

  5. #5
    Join Date
    Aug 2003
    Posts
    328
    Thats what I was afraid of. Orqacle makes it so easy.
    Thanks for your time.

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    I just re-read your sig line. I nearly spit coffee all over the keyboard. Thanks for starting my day off with a laugh.

    :-)


    Originally posted by Enigma
    Look up convert in the Holy Book (SQL Server Books Online)

  7. #7
    Join Date
    Aug 2003
    Posts
    328
    Why? And I'm glad you were amused.

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by hmscott
    Usually, CONVERT is used to transform a date/time into a char or varchar data type. Looking at it, I don't see anything that would immediately allow you to take a string an convert it to date/time.

    Regards,

    hmscott

    how about

    select convert(varchar,convert(datetime,'20031201'),101)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Aug 2003
    Posts
    328
    I need to put a column name in there. If I put select convert(char(10),column_name,101) from table_name, I just get the same string I had before.

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by exdter
    I need to put a column name in there. If I put select convert(char(10),column_name,101) from table_name, I just get the same string I had before.
    Use
    Code:
    select convert(varchar(10),convert(datetime,column_name),101)
    Last edited by Enigma; 11-24-03 at 11:32.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Aug 2003
    Posts
    328
    This is what I get:
    Server: Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type datetime.

  12. #12
    Join Date
    Dec 2002
    Posts
    1,245
    Enigma:
    I stand corrected. I had never seen that before. It seems to only work if the data is formatted YYYYMMDD (or YYMMDD). Is that correct, or is there an option to specify the order of characters in the date string?

    As for your sig, there's a classic definition of humor, that I can't recall right now, something to do with continuity and perception and cognition. Anyway, it met that definition.

    Regards,

    hmscott

  13. #13
    Join Date
    Dec 2002
    Posts
    1,245
    Try this:

    Code:
    declare @DateString varchar(8)
    
    select @DateString = '20030731'
    
    select convert(datetime, @DateString)

    Originally posted by hmscott
    Enigma:
    I stand corrected. I had never seen that before. It seems to only work if the data is formatted YYYYMMDD (or YYMMDD). Is that correct, or is there an option to specify the order of characters in the date string?

    As for your sig, there's a classic definition of humor, that I can't recall right now, something to do with continuity and perception and cognition. Anyway, it met that definition.

    Regards,

    hmscott

  14. #14
    Join Date
    Aug 2003
    Posts
    328
    I'm not sure I follow you. The string I have is in number format and is in the 'yyyymmdd' format.

  15. #15
    Join Date
    Aug 2003
    Posts
    328
    I need to put a column name there.

Posting Permissions

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