November 20, 2012 at 7:30 am
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
November 20, 2012 at 7:37 am
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/
November 20, 2012 at 7:38 am
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
November 20, 2012 at 7:54 am
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
November 20, 2012 at 7:59 am
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/
November 20, 2012 at 8:03 am
Thank you guys for trying to help me out,
I'm trying to get the ddl,insert statements..give me a minute
Thxs
SM
November 20, 2012 at 8:12 am
********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
November 20, 2012 at 8:14 am
I have deleted a lot of columns names..while pasting it ..
November 20, 2012 at 8:16 am
in the above code the date column is [I353_DATE_TRANSACTION]
November 20, 2012 at 8:23 am
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
November 20, 2012 at 8:40 am
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
November 20, 2012 at 8:51 am
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
November 20, 2012 at 9:13 am
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
November 20, 2012 at 9:17 am
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
November 20, 2012 at 9:19 am
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