Post reply

Get Date,Month from varchar dates

  • January 26, 2011 at 2:49 pm

    #233412

    I have dates stored as varchar(8) -- 113010 i.e mmddyy

    Timevarchar(6)--164018

    I need to convert these to the following

    monthMonth number of transaction (1-12)

    dayDay number of transaction (1-31)

    hourHour number of transaction (military)(0-23)

    minuteMinute number of transaction (0-59)

    dateTimestamp of transacation

    Thanks,

  • January 26, 2011 at 3:05 pm

    #1278817

    I have the answer for the date part of the question; we have some tables with the same date style.

    we use the convert function to get it into a datetime format then use the month(), DAY() and Year() functions to get the parts.

    Declare @workDate as DateTime

    Declare @workMonth as varchar(2)

    declare @workYear as varchar(4)

    Select @workdate = Convert(datetime, MySQLDate, 101)

    set @workmonth = month(@workdate)

    set @workYear = Year(@workdate)

    There are convert() formats for Time, but I have not used that functionality yet.

    Good Luck

  • January 26, 2011 at 3:26 pm

    #1278826

    I'm sorry but I have to ask: since you posted on a SS2K8 version, why don't you use the DATE and TIME data type to store the info you need?

    If you're not at SS2K8 yet, use DATEIME or SMALLDATETIME instead.

    Assuming you don't have a proper check constraint implemented, what would be the time for the value 'crap' (being perfectly valid as a varchar(6)) or 'bu....it' as a date?

    I strongly recommend to use a data type that actually matches the content of the column.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 26, 2011 at 3:43 pm

    #1278832

    I was not the one to create the tables.

  • January 26, 2011 at 3:50 pm

    #1278842

    PSB (1/26/2011)

    I was not the one to create the tables.

    "It wasn't me" is not a valid answer. 😉

    The question is: can you change it? If not the column data type, can you add another persisted computed column (yes, using more storage space...) that'll check if the value in the source column is a valid date and if yes, convert it?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 26, 2011 at 4:24 pm

    #1278855

    I will confirm that if I can add persisted columns. Can you please explain how to do that ?

  • January 27, 2011 at 10:38 am

    #1279223

    PSB (1/26/2011)

    I will confirm that if I can add persisted columns. Can you please explain how to do that ?

    Here's one way how to do it.

    You could also search the web for "persisted computed column" for more links.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 27, 2011 at 10:47 am

    #1279231

    I have done that using substring function.

    Thanks,

    PSB

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply