Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Help with doing “Cast the decimal as string, parse into the format specified, and then cast back into datetime”? Expand / Collapse
Author
Message
Posted Tuesday, November 20, 2012 7:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 2:23 PM
Points: 48, Visits: 100
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
Post #1386904
Posted Tuesday, November 20, 2012 7:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 13,481, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1386910
Posted Tuesday, November 20, 2012 7:38 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1386911
Posted Tuesday, November 20, 2012 7:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:30 PM
Points: 12,918, Visits: 32,089
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1386925
Posted Tuesday, November 20, 2012 7:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 13,481, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1386930
Posted Tuesday, November 20, 2012 8:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 2:23 PM
Points: 48, Visits: 100
Thank you guys for trying to help me out,

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

Thxs
SM
Post #1386933
Posted Tuesday, November 20, 2012 8:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 2:23 PM
Points: 48, Visits: 100

********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

Post #1386937
Posted Tuesday, November 20, 2012 8:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 2:23 PM
Points: 48, Visits: 100
I have deleted a lot of columns names..while pasting it ..
Post #1386939
Posted Tuesday, November 20, 2012 8:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 2:23 PM
Points: 48, Visits: 100
in the above code the date column is [I353_DATE_TRANSACTION]
Post #1386941
Posted Tuesday, November 20, 2012 8:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 2:23 PM
Points: 48, Visits: 100
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


Post #1386946
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse