Help with doing “Cast the decimal as string, parse into the format specified, and then cast back into datetime”?

  • Hello Fellow developers,

    I'm a rokee when it comes to T-sql.

    I have been trying to create a view from a table(lets call it table X).The problem is one of the date columns has been defined as decimal datatype.

    And I want that particular column to display date in this format "01/31/2012".Currently its in this 'YYYYMMDD"

    I tried this "select CONVERT (varchar, CONVERT(datetime,STR(MY_COLUMN)),101)

    FROM mytable;" it doesn't give the desired tresult cause the datatype is decimal

    I have been trying to do the following to achieve it ,I'm not familiar with T-sql

    “Cast the decimal as string, parse into the format specified, and then cast back into datetime”

    Can someone help me with code to achieave this “Cast the decimal as string, parse into the format specified, and then cast back into datetime”

    Thnaks

    SM

  • SQL server rokee (11/20/2012)


    Hello Fellow developers,

    I'm a rokee when it comes to T-sql.

    I have been trying to create a view from a table(lets call it table X).The problem is one of the date columns has been defined as decimal datatype.

    And I want that particular column to display date in this format "01/31/2012".Currently its in this 'YYYYMMDD"

    I tried this "select CONVERT (varchar, CONVERT(datetime,STR(MY_COLUMN)),101)

    FROM mytable;" it doesn't give the desired tresult cause the datatype is decimal

    I have been trying to do the following to achieve it ,I'm not familiar with T-sql

    “Cast the decimal as string, parse into the format specified, and then cast back into datetime”

    Can someone help me with code to achieave this “Cast the decimal as string, parse into the format specified, and then cast back into datetime”

    Thnaks

    SM

    You truly defined the cause of the problem.

    The problem is one of the date columns has been defined as decimal datatype.

    This type of thing will cause you nothing but grief until one day it is decided to store datetime data in a datetime column. I realize that sometime these things are outside of our control and we have to plug our nose and move forward.

    I can help but first you have to help me. I need to see ddl (create table statement), sample data (insert statements) and what you actually want as desired output based on your sample data. Take a look at the first link in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Can you provide a sample and definition of the data?

    You say it's in decimal format, but don't define what the integer part of the number is nor what the decimal part is. Can't help without at least that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • based on the other thread for the same issue the problem is SOME of his data is YYYYMMDD as a decimal, like 20121225, but some of his data is not in the same format, and as a result, he gets an error converting the STR({somedecimal}) to a datetime.

    i think the key there is to look at the values that are not convertable to datetime;

    something like SELECT * From MyTable Where LEN(STR(MyDateField)) <> 8 for starters.

    beating the dead horse one more time, the fix is to use datetime columns for datetime values. it would be best to take the time to fix this issue...and it's probably not just this one column that is suffering from the issue...you might create a work around for this one column, only to have it show up again tomorrow on a different table or column.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (11/20/2012)


    based on the other thread for the same issue the problem is SOME of his data is YYYYMMDD as a decimal, like 20121225, but some of his data is not in the same format, and as a result, he gets an error converting the STR({somedecimal}) to a datetime.

    i think the key there is to look at the values that are not convertable to datetime;

    something like SELECT * From MyTable Where LEN(STR(MyDateField)) <> 8 for starters.

    beating the dead horse one more time, the fix is to use datetime columns for datetime values. it would be best to take the time to fix this issue...and it's probably not just this one column that is suffering from the issue...you might create a work around for this one column, only to have it show up again tomorrow on a different table or column.

    🙂 Didn't realize this was the same as yesterday's thread on forcing a decimal into a datetime failing. We can help you but without some details we are shooting in the dark.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thank you guys for trying to help me out,

    I'm trying to get the ddl,insert statements..give me a minute

    Thxs

    SM

  • ********This is the DDL***************

    USE [stgFACTS]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[I353_SalesOrder](

    [I353_DATE_TRANSACTION] [decimal](8, 0) NULL,

    [I353_TIME_TRANSACTION] [decimal](6, 0) NULL,

    CONSTRAINT [PK_I353_SalesOrder] PRIMARY KEY CLUSTERED

    (

    [I353 _ITEM_NBR] ASC,

    [I353 _SHIP_SUFFIX] ASC,

    [I350_SALE_ORDER_NBR] ASC,

    [I351 _ITEM_NBR] ASC,

    [IDMS_UPDATE_TIME] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    Insert statements

    INSERT INTO [stgFACTS].[dbo].[I353_SalesOrder]

    (

    ,[I353_DATE_TRANSACTION]

    ,[I353_TIME_TRANSACTION]

    VALUES

    (,<I353_DATE_TRANSACTION, decimal(8,0),>

    ,<I353_TIME_TRANSACTION, decimal(6,0),>

    ,<ACTIVE_FLAG, nvarchar(1),>)

    GO

    ******************************************************************

    Output looks like (its in YYYYMMDD order I want it in MM/DD/YYYY order)

    20120910

    20120910

    20120913

    20120913

    20120917

    20120917

    20120925

    20120926

    20121001

    20121001

    20121003

    20121004

    20121004

    20121004

    20121008

    20121010

    20121029

    20120917

    20030515

    20030604

    20120806

    20120806

    20120806

    20120827

    20120904

    20120904

    20120904

    20120905

    20120906

    20120924

    20120924

    20120924

    20120925

    20120925

    20121008

    20121008

    20121008

    20121008

    20121015

    20121024

    0

    20120912

    20120912

    20120912

    20120914

    20120914

    20120914

    20120914

    20120914

    20120914

    20031119

    20040112

    20040113

    0

    20120920

    20120920

    20121002

    20121005

    20121017

    20121018

    20121018

    20121026

    20121102

    20030909

    20120914

    20121011

    20030604

    20031103

    20120918

    20121015

    0

    20121018

    0

    20121009

    20121009

  • I have deleted a lot of columns names..while pasting it ..

  • in the above code the date column is [I353_DATE_TRANSACTION]

  • I have broken down the reqs into 3 parts

    1)Cast decimal as string ( which I did)

    select convert(varchar,convert(decimal(8,0),[I353_DATE_TRANSACTION]))

    from [stgFACTS].[dbo].[I353_SalesOrder]

    2), parse into the format specified(which is MM/DD/YYYY....I don’t know how to do that..f)

    3)and then cast back into datetime” (I don’t know how to do that)

    Thx

    SM

  • My take! Obviously that means the 0's will end up being date 1900/01/01 and I guess you could substitute that with NULL or otherwise acceptable value. Apologies if some of the converts are superfulous.

    SELECT

    CASE WHEN I353_DATE_TRANSACTION > 19000000 AND I353_DATE_TRANSACTION < 21000000 THEN

    CONVERT(DATETIME,CONVERT(VARCHAR,CONVERT(INT,I353_DATE_TRANS)))

    ELSE CONVERT(DATETIME,'1900-01-01')

    END NEW_DATE_COLUMN

    FROM WHATEVER_THAT_TABLE_NAME_WAS

  • Splendid Patrick,

    That is what I was looking for, Only thing is the out put is in YYYY-MM-DD format , how can I change it to MM/DD/YYYY..and the out is NEW_DATE_COLUMN

    2012-09-10 00:00:00.000

    2012-09-10 00:00:00.000

    2012-09-13 00:00:00.000

    2012-09-13 00:00:00.000

    2012-09-17 00:00:00.000

    2012-09-17 00:00:00.000

    2012-09-25 00:00:00.000

    2012-09-26 00:00:00.000

    2012-10-01 00:00:00.000

    2012-10-01 00:00:00.000

    I want to get rid of 00:00:00.000 from the final output..

    Thanks

    SM

  • You can access the various convert options on "books on line" in SQL Studio by searching for 'convert', and you can see that you do have some options for formatting the resulting date back to a string for reporting.

    You could also probably chew up the result using "substring" and such, I do that often too.

    I'm in the same boat as you, we have columns here containing dates as integers and as is probably the case with you, we're not about to change the column type.

    This uses one more convert statement. Note the "101" argument to convert. Notice I also changed the value in the 'ELSE' clause to a string with the same format as leaving it as a convert datetime changes the type of the entire expression. Interesting stuff!

    SELECT

    CASE WHEN I353_DATE_TRANSACTION > 19000000 AND I353_DATE_TRANSACTION < 21000000 THEN

    CONVERT(VARCHAR(12),

    -- THE ORIGIONAL CONVERT FROM PREVIOUS POST

    CONVERT(DATETIME,CONVERT(VARCHAR,CONVERT(INT,I353_DATE_TRANSACTION)))

    -- END OF THE ORIGINAL CONVERT

    ,101)

    ELSE '01/01/1900'

    END NEW_DATE_COLUMN

    FROM

    YOUR_ORIGINAL_TABLE_NAME

  • Patrick,

    Thanks Buddy..you are the man ..the code worked like a charm.Can't thank you enough,I have been beating my head to solve this.

    Regards

    Swarup

  • Glad to help, we've all been there. SQL Server is an interesting topic to be sure!

Viewing 15 posts - 1 through 15 (of 27 total)

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