What 30 days Data only
-
Hi All
I want only 30 days data on my cube and i run this query on my fact table
DELETE FROM <Table Name>
WHERE CAST((SUBSTRING(CAST(dateinvoiced AS char), 4, 2) + '/' + SUBSTRING(CAST(dateinvoiced AS char), 6, 2)
+ '/' + SUBSTRING(CAST(19000000 + dateinvoiced AS char), 1, 4)) AS Datetime) >= GETDATE() - 30
I used cast and substring funcation because i have dateinvoiced field in int type like(1060313)
but when i run my query it shows error msg that
"Syntax error converting datetime from character string. This query working on my laptop but not on server.
Thanks
Vandy
-
If you store your date as an int like 1060313, then what does 1060313 mean?
Is it seconds, minutes hours..? Starting from when?
/Kenneth
-
It seems that the "1" at the beginning is a nonsense (or has some meaning not connected with the date... most probably it is there to avoid losing the leading 0 in an integer
) and the rest is 06-03-13.. as 2006/03/13.If that's it, please try this:
WHERE CAST(('20' + RIGHT(CAST(dateinvoiced as varchar(10)),6)) AS datetime)
In this order it works regardless of language/date settings. Skip all the / or - and produce a clean, nice YYYYMMDD format, that is always understandable on any computer. Seems that laptop has different regional settings than the other PC where you ran the SQL.
PS: You didn't explain how the date is coded, so I can not guarantee that this will always produce a correct result... If you want to be sure, please be more specific about the dateinvoiced column in your table.
Anyway, I'd strongly suggest to store the value in standard DATETIME format, if you can influence it somehow. Storing date as char or integer is not a good idea.
EDIT : I got it! The 1 is added to the 19, making the century together. OK, now that will require some testing to put it properly together... I'll post a correction later.
-
Oh oh... how do you store dates from previous century (1999 etc.)? Are there none, or are they simply missing the "century digit"... like 60313 meaning 1906-03-13?
So far I've come up with this solution that was tested on various dates and worked for both centuries:
select CAST((CAST((19 + dateinvoiced/1000000) AS CHAR(2)) + RIGHT('000000' + CAST(dateinvoiced as varchar(10)),6)) AS datetime)
But again, couldn't you do something about it? This really a terrible way of storing datetime.. as you can see for yourself. Instead of a plain query you have to write this construct... not speaking about possible performance degradation because indexes will often not be used and numerous conversions will be performed.
-
Sorry for not explaning
Yeah 1 is for century rest 06 for 2006 and then month and day.
Last century dates start with 9
My posted query working well on my laptop but not on server everything is same.
Thanks for ur query will try it.
-
Hmm... you say that entries from last century begin with 9, but as can be seen from your code, they only have 6 digits (otherwise the result would be nonsense). As I understand it, the person who designed the database realized that in this century leading zero would be missing and "corrected" it by adding digit 1 at the beginning. That is, 15.9.1991 is stored as 910915, and 15.9.2001 is stored as 1010915. Is this correct?
If yes, then my posted query will work all right for them.
Your query produces (before conversion to datetime) result in this format for entries from last century : 31/3 /1996. Mark the space after 3... that is not correct and causes the error you've posted. Is it possible that you have only part of the data on your laptop, and all the data are from this century, while on the server there are also dates from last century?
One more thing I didn't mention about your code : you should always define character data types completely, including length. I know there is default length that is applied if you don't mention it explicitly, but relying on SQLS defaults is the best way to create problems in the future - and it will be problems that are rather hard to locate, because you simply start getting wrong results - sometimes. One example from BOL : When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30. Now that is enough to create some confusion, but what if these values change in a future version od SQL Server? Will you scan all your code, check what it does and rewrite it when necessary?
-
Hi
Sorry for delay
Actually just started my job it's scary
they have AS400 Main Database i am doing extraction for SQL Server from AS400. I execute ur qry but error was out of range.
date stored for1 Jan 1998 is Like 980101
DateInvoiced is in int type having 4 lenght.
-
Hi
It's working now
DELETE FROM BillingF
WHERE CAST((SUBSTRING(CAST(dateinvoiced AS char), 4, 2) + '/' + SUBSTRING(CAST(dateinvoiced AS char), 6, 2)
+ '/' + SUBSTRING(CAST(19000000 + dateinvoiced AS char), 1, 4)) AS Datetime) >= GETDATE() -30
same query but diference in the spaces only.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply