October 9, 2007 at 8:07 pm
This issue happens once in a blue moon and it would be great if someone can shed some light on it: A date string like the following '2007-10-03' gets inserted into a datetime field (into a permanent table) but becomes '2007-03-10'. This only happens in our Live environment as part of a larger script and I can never replicate it. Obviously a bug but how can it be so sporadic. Here's the script:
DECLARE @bDebug BIT; SET @bDebug = 0
DECLARE @vchDestinationAccountNumber VARCHAR(20)
DECLARE @iCashTransferDelayInMinutes INTEGER
CREATE TABLE WORKING..xxxx
(
iContractNoteId INTEGER,
vchAsxCode VARCHAR(10),
iAccountNumber INTEGER,
vchType VARCHAR(1),
dtContractDate DATETIME,
dtSettlementDate DATETIME,
decUnits DECIMAL(18,6),
decTotalAmount DECIMAL(18,2),
decPrice DECIMAL(18,6),
decBrokerage DECIMAL(18,2),
decGST DECIMAL(18,2),
--
vchReferenceNumber VARCHAR(20),
vchReferenceNumber2 VARCHAR(20),
decTotalMinusRITC DECIMAL(18,2),
decTotalNoBrokerage DECIMAL(18,2),
decBrokerageWithGSTMinusRITC DECIMAL(16,2),
--
iTaxServiceId INTEGER,
iProductAccountId INTEGER,
iExpenseReserveAccountId INTEGER,
iExpenseReserveHoldingId INTEGER,
iExpenseReserveCMTAccountId INTEGER,
vchExpenseReserveCMTAccountNumber VARCHAR(20),
iInvestorsCMTAccountId INTEGER,
vchInvestorsCMTAccountNumber VARCHAR(20),
iInvestmentAccountId INTEGER,
iCustodyShareServiceId INTEGER,
iCustodyShareHoldingId INTEGER,
iHinAccountId INTEGER,
iHinCustodyShareHoldingId INTEGER,
iPooledCashServiceId INTEGER,
iPooledCashHoldingId INTEGER,
iUnsettledBuyHoldingId INTEGER,
--
iNewUnsettledBuyHoldingId INTEGER,
iNewCustodyShareHoldingId INTEGER,
iNewHinCustodyShareHoldingId INTEGER,
iNewUnsettledTransactionId INTEGER,
iNewUnsettledEntryId INTEGER,
iNewSharesTransactionId INTEGER,
iNewSharesEntryId INTEGER,
iNewBrokerageEntryId INTEGER, -- use iNewSharesTransactionId for iTransactionId
iNewActualSharesTransactionId INTEGER,
iNewActualSharesEntryId INTEGER,
iNewSettledTransactionId INTEGER,
iNewSettledEntryId INTEGER,
iNewExpenseReserveOutTransactionId INTEGER,
iNewExpenseReserveOutEntryId INTEGER,
iNewExpenseReserveInTransactionId INTEGER,
iNewExpenseReserveInEntryId INTEGER,
iNewPooledCashTransactionId INTEGER,
iNewPooledCashEntryId INTEGER,
iNewJournalId INTEGER,
iNewCustodyShareId INTEGER,
iNewCustodyShareContractNoteId INTEGER,
iNewHoldId INTEGER,
iNewTransferIdOut INTEGER,
iNewTransferIdIn INTEGER,
iNewTransTraceKeyOut INTEGER,
iNewTransTraceKeyIn INTEGER
)
--
--INSERT INTO #tblTempBadEntries
-- (iEntryId, iHoldingId)
SET @vchDestinationAccountNumber = '000000000'
SET @iCashTransferDelayInMinutes = 90
-- formatcsv -i32916_buy.csv -f" UNION SELECT ~0, ~s1, ~2, ~s4, ~d5, ~d6, ~7, ~8, ~9, ~10, ~11" > 32916_buy.txt
INSERT INTO WORKING..xxxx
(iContractNoteId, vchAsxCode, iAccountNumber, vchType, dtContractDate, dtSettlementDate, decUnits, decTotalAmount, decPrice, decBrokerage, decGST)
SELECT 27721651, 'PNA', 999999, 'B', '2007-10-03', '2007-10-09', 180000, 147600.00, 0.82, 0, 0
October 9, 2007 at 8:13 pm
SET DATEFORMAT ymd
You obviously have DATEFORMAT set to "dmy", so it reads '2007-10-03' as 10 March 2007.
If you can change format of dates in files to YYYYMMDD (no dashes). 20071003 will be interpreted one way only independently of DATEFORMAT settings.
_____________
Code for TallyGenerator
October 9, 2007 at 9:02 pm
I was about to discount your suggestion because I believed I was always opening a new instance of query analyzer and the previous SET DATEFORMAT dmy wouldnt be relevant. But I think I have occaisionally been opening the query from a file within a qa instance that has retained the SET DATEFORMAT dmy.. if that makes sense. So thanks, I think you have hit the nail on the head.
October 9, 2007 at 9:04 pm
Sergiy, wouldn't that be storing a date as a char datatype? Why would you ever not want to use datetime datatype? I have always thought it was best to store dates as dates. What would be the best way to do the conversion if you wanted to do what you suggested, and would you have to convert back to datetime format for date calculations?
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 9, 2007 at 9:58 pm
I believe it was about reading dates from files.
Unfortunately there is no unified way to pass date-time values in files, so we need always to agree on some format used in each particular interface.
_____________
Code for TallyGenerator
October 10, 2007 at 7:25 am
We never input dates in the way that SQL Server stores them in a datetime.
'20071003' is just as much a 'date' as '2007-10-03'
But only the first format is independant of language or dateformat settings, thus making this the preferred format for input.
Tibor has written a nice article explaining most about dates and how it works.
Well worth the read.
http://www.karaszi.com/SQLServer/info_datetime.asp
/Kenneth
October 11, 2007 at 2:01 am
If you are inputting your dates from a front end through ADO then you can and should pass dates as dates. If you must use strings then I always would include SET DATEFORMAT or an explicit and unambiguous CONVERT.
As a general rule, never rely on database settings for anything if you can be explicit in your stored procedure.
October 11, 2007 at 2:39 am
If you are inputting your dates from a front end through ADO then you can and should pass dates as dates. If you must use strings then I always would include SET DATEFORMAT or an explicit and unambiguous CONVERT.
And the only unambigous dateformat as input is 'ssyymmdd'
As a general rule, never rely on database settings for anything if you can be explicit in your stored procedure.
Totally agree. Always be explicit, never rely on default settings or behaviour. Ever...
/Kenneth
October 11, 2007 at 3:34 am
Stewart Joslyn (10/11/2007)
If you are inputting your dates from a front end through ADO then you can and should pass dates as dates.
What do you mean when saying dates?
In MS SQL Server dates are float values expressing number of days passed from 1/1/1900.
Quite different from what is used for dates in front end programming languages.
Unfortunately here it's more messy than in character expressions for dates.
What you really need is to specify date format you use in every particular interface and ALWAYS use EXPLICIT conversion to dates using specified format.
CONVERT (datetime, @DateString, 121) will always work correctly, no matter what language or dateformat settings you've got.
_____________
Code for TallyGenerator
October 11, 2007 at 3:40 am
ADO has a Date datatype which can be passed to a SQL Server parameter and works fine both from Visual Basic 6 and in C# and VB.NET. Presumably in all other .NET variants but I haven't checked.
In VB6 dates also are stored as numerics but the base date is 31Dec1899 so, if you pass dates like this, you'll end up out by 1 day !
October 11, 2007 at 4:19 am
I know "date Zero" is 1899-12-30 in both Access AND VB6. Not 1899-12-31.
So you are off by two days.
N 56°04'39.16"
E 12°55'05.25"
October 11, 2007 at 4:54 am
CONVERT (datetime, @DateString, 121) will always work correctly, no matter what language or dateformat settings you've got.
Perhaps this need a little clarification?
It rides on the fact that @DateString contains a string with a date that is delimited the same as the style 121 (ie '2003-02-28'). Then it works.
But should @DateString have a date formatted according some other style, it may break.
'02/28/2003' seems to work pretty well, but if we set language to British and use '28/02/2003' it breaks.
So, my point is, we still need to be very careful what the 'formatted inputdate-string' looks like.
AFAIK, there's not a single format using delimiters that is not either language or datesetting sensitive.
The only 'safe' format is, as you mentioned earlier, '20030228' (ssyymmdd)
Also, this is in the context on 'how to delimit/format dates for database entry', not when you juggle them around in some other environment like .NET or ADO or elsewhere.
/Kenneth
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply