SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
SQL SERVER ROOKIE
SQL SERVER ROOKIE
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59832 Visits: 17947
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.

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)
GSquared
GSquared
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55233 Visits: 9730
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
Lowell
Lowell
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68438 Visits: 40898
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!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59832 Visits: 17947
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.

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)
SQL SERVER ROOKIE
SQL SERVER ROOKIE
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 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
SQL SERVER ROOKIE
SQL SERVER ROOKIE
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 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),>Wink
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
SQL SERVER ROOKIE
SQL SERVER ROOKIE
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 100
I have deleted a lot of columns names..while pasting it ..
SQL SERVER ROOKIE
SQL SERVER ROOKIE
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 100
in the above code the date column is [I353_DATE_TRANSACTION]
SQL SERVER ROOKIE
SQL SERVER ROOKIE
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search