Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2011
    Posts
    78

    Unanswered: Spliting the date into year, month and day

    What can I use to split the data below:

    For example, 2011-06-07 14:36:14.000

    How do I split the date and the time in this case?

    When I have 2011-06-07, how do I split the the year, month and day?

    Please help. Thank you.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    declare	@DateText varchar(50)
    set	@DateText = '2011-06-07 14:36:14.000'
    
    select CONVERT(datetime, @DateText)
    select CONVERT(date, @DateText)
    select CONVERT(time, @DateText)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2011
    Posts
    78
    Thank you so much. If I have an entire column of datetime, do replace 2011-06-07 14:36:14.000 with the column name? I want to split all the time in the column into date and time, not just one date time.

    Please let me know. Thank you.



    like

    set @DateText = 'Airtimedate'



    Quote Originally Posted by blindman View Post
    Code:
    declare	@DateText varchar(50)
    set	@DateText = '2011-06-07 14:36:14.000'
    
    select CONVERT(datetime, @DateText)
    select CONVERT(date, @DateText)
    select CONVERT(time, @DateText)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I recommend you just change your column type to DateTime (assuming it is a text string at the moment).
    Once it is stored as a true DateTime datatype, you can split it or manipulate it as necessary when you access the data. I do not recommend storing it in split columns.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2011
    Posts
    78
    I don't have access to modify the table properties.
    Right now, I think everything is in views. I am selecting all these out of views.

    How do I go about changing the entire column's datetime into date and time?

    Thank you.

  6. #6
    Join Date
    Jul 2011
    Posts
    78
    Never mind about the question above this, I used the convert function to figured it out.

  7. #7
    Join Date
    Sep 2010
    Posts
    153

    Smile here is teh query if u already know the date

    select year ('2011-06-07 14:36:14.000') as year, datepart (mm, '2011-06-07 14:36:14.000') as month, datepart (dd, '2011-06-07 14:36:14.000') as day

  8. #8
    Join Date
    Jul 2011
    Posts
    78
    Thank you for the response =)

Posting Permissions

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