June 28, 2012 at 6:20 pm
Hello All,
The table I need to pull data from has a column called LASTUPDATE(datetime). When a new balance is reached for the data, instead of adding a new entry in the table, it updates an exiting row with the current balance, and updates the LASTUPDATE column with the date. In my query, I need to do a beginning balance and an ending balance.
In order to get the beginning balance for the day, I need to get the ending balance from the previous day. Therefore, I need to get all the balance updates where the LASTUPDATE(datetime) < the current system date GETDATE()
Select
DENOMBAL, LASTUPDATE
Where
LASTUPDATE < GETDATE()
The system will not allow me to do that unless I convert the LASTUPDATE and GETDATE() fields to an int. When I try that, I get the following error:
Msg 8115, Level 16, State 2, Line 5
Arithmetic overflow error converting expression to data type datetime.
My query for beginning balance:
DECLARE @CurDat int -- Declare Current Date
Select @CurDat = CONVERT(INT, CONVERT(CHAR(10), GETDATE(), 112))
--Print @CurDat (I used this to verify the conversion of the system date)
Select
"DENOMINATION"."VALUE" * "CASH"."NBDENOM" AS "Denomination Total",
convert(int, convert(char(10), "CASH"."LASTUPDATE", 112)) AS "Last Count Update"
From
maestro."CASHCENTRE" "CASHCENTRE"
Where
"CASH"."LASTUPDATE" < @CurDat
My query for the ending balance will leave off the where clause.
Thanks for the help.
June 28, 2012 at 6:32 pm
It would help if you could provide the DDL (CREATE TABLE statement) for the table, some sample data as a series of INSERT INTO statements (sample date means just taht, sample data, not real data from your system), and the expected results based on the sample data.
July 1, 2012 at 2:26 pm
cadm777 (6/28/2012)
Hello All,The table I need to pull data from has a column called LASTUPDATE(datetime). When a new balance is reached for the data, instead of adding a new entry in the table, it updates an exiting row with the current balance, and updates the LASTUPDATE column with the date. In my query, I need to do a beginning balance and an ending balance.
In order to get the beginning balance for the day, I need to get the ending balance from the previous day. Therefore, I need to get all the balance updates where the LASTUPDATE(datetime) < the current system date GETDATE()
Select
DENOMBAL, LASTUPDATE
Where
LASTUPDATE < GETDATE()
The system will not allow me to do that unless I convert the LASTUPDATE and GETDATE() fields to an int. When I try that, I get the following error:
Msg 8115, Level 16, State 2, Line 5
Arithmetic overflow error converting expression to data type datetime.
My query for beginning balance:
DECLARE @CurDat int -- Declare Current Date
Select @CurDat = CONVERT(INT, CONVERT(CHAR(10), GETDATE(), 112))
--Print @CurDat (I used this to verify the conversion of the system date)
Select
"DENOMINATION"."VALUE" * "CASH"."NBDENOM" AS "Denomination Total",
convert(int, convert(char(10), "CASH"."LASTUPDATE", 112)) AS "Last Count Update"
From
maestro."CASHCENTRE" "CASHCENTRE"
Where
"CASH"."LASTUPDATE" < @CurDat
My query for the ending balance will leave off the where clause.
Thanks for the help.
We can't see what you can see without the table with sample data 🙁
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 2, 2012 at 1:55 pm
To Lynn, CELKO, and rhythmk
My apologies for not positing the table script and sample data. I hope I didn't frustrate anyone. I got pulled off this project for a production issue, so that is why it took me so long to get back to this.
Lynn,
I followed your link for Forum etiquette. I'm in the process of reading it now. I'll follow that from now on. http://www.sqlservercentral.com/articles/Best+Practices/61537/
Also, with the help of one of CELKO's queries the had the BETWEEN option in the query, somehow triggered my mind to check the data type on the date fields. I guess a little rest makes a difference. That is when I noticed I was converting the system date. I copied and pasted the get date query from another query I used to use and not paying attention to the conversion.
I changed the way I pulled the system date from:
DECLARE @CurDat int -- Declare Current Date
Select @CurDat = CONVERT(INT, CONVERT(CHAR(10), GETDATE(), 112))
To:
DECLARE @CurDat Date -- Declare Current Date
Select @CurDat = GETDATE()
Then I was able to pull the data by using:
WHERE
CASH.LASTUPDATE < @CurDat
CELKO,
I like your explanation. I learned something about using dates as a continuum. However, I don't think my vendor uses that. Next time I'll have the script and sample data ready.
Here is my script for the table with the keys and constraints. I changed a few names in the script out of respect for my vendor. This also has 11 insert statements. I have obfuscated the data some.
USE [InventoryDB]
GO
/****** Object: Table [inven].[CASH] Script Date: 07/02/2012 12:20:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [inven].[CASH](
[varchar](31) NOT NULL,
[HIDDEN] [varchar](31) NOT NULL,
[CREATION] [datetime] NOT NULL,
[LASTUPDATE] [datetime] NOT NULL,
[WORKSTATIONID] [varchar](64) NOT NULL,
[LUSERUPDATE] [varchar](31) NOT NULL,
[SEQNUMBER] [numeric](10, 0) NOT NULL,
[LUSER] [varchar](31) NULL,
[LZONE] [varchar](31) NULL,
[LSAFE] [varchar](31) NULL,
[NBDENOM] [numeric](10, 0) NOT NULL,
[LDENOM] [varchar](31) NOT NULL,
[TRANSFER] [numeric](10, 0) NULL,
[LCASHCENTRE] [varchar](31) NOT NULL,
CONSTRAINT [CASH_LINK_PK] PRIMARY KEY CLUSTERED
(
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [INVEN_DATA],
CONSTRAINT [CASH_UNIQUE_IDX] UNIQUE NONCLUSTERED
(
[LUSER] ASC,
[LZONE] ASC,
[LSAFE] ASC,
[LDENOM] ASC,
[TRANSFER] ASC,
[LCASHCENTRE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [INVEN_INDEX]
) ON [INVEN_DATA]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [inven].[CASH] WITH CHECK ADD CONSTRAINT [CASH_LDENOM_FK] FOREIGN KEY([LDENOM])
REFERENCES [inven].[DENOMINATION] ()
GO
ALTER TABLE [inven].[CASH] CHECK CONSTRAINT [CASH_LDENOM_FK]
GO
ALTER TABLE [inven].[CASH] WITH CHECK ADD CONSTRAINT [CASH_LSAFE_FK] FOREIGN KEY([LSAFE])
REFERENCES [inven].[SAFE] ()
GO
ALTER TABLE [inven].[CASH] CHECK CONSTRAINT [CASH_LSAFE_FK]
GO
ALTER TABLE [inven].[CASH] WITH CHECK ADD CONSTRAINT [CASH_LUSER_FK] FOREIGN KEY([LUSER])
REFERENCES [inven].[USERS] ()
GO
ALTER TABLE [inven].[CASH] CHECK CONSTRAINT [CASH_LUSER_FK]
GO
ALTER TABLE [inven].[CASH] WITH CHECK ADD CONSTRAINT [CASH_LUSERUPDATE_FK] FOREIGN KEY([LUSERUPDATE])
REFERENCES [inven].[USERS] ()
GO
ALTER TABLE [inven].[CASH] CHECK CONSTRAINT [CASH_LUSERUPDATE_FK]
GO
ALTER TABLE [inven].[CASH] WITH CHECK ADD CONSTRAINT [CASH_LZONE_FK] FOREIGN KEY([LZONE])
REFERENCES [inven].[ZONE] ()
GO
ALTER TABLE [inven].[CASH] CHECK CONSTRAINT [CASH_LZONE_FK]
GO
INSERT into inven.CASH values(0661999352_2012-01-22_12:36:34,000000000000000000000000000000,2012-01-22 12:36:34.000,2012-06-13 16:43:33.000,CPU2,4703995492_2011-11-10_14:00:02,69,NULL,NULL,4709995293_2011-11-10_14:15:43,12031,0015000000_2011-07-13_23:15:59,0,0142999875_2011-07-14_07:37:19)
INSERT into inven.CASH values(0661999354_2012-01-22_12:36:34,000000000000000000000000000000,2012-01-22 12:36:34.000,2012-06-15 16:46:03.000,CPU2,4703995492_2011-11-10_14:00:02,94,NULL,NULL,4709995293_2011-11-10_14:15:43,8182,0014999998_2011-07-13_23:14:50,0,0142999875_2011-07-14_07:37:19)
INSERT into inven.CASH values(0663999344_2012-01-22_12:39:30,000000000000000000000000000000,2012-01-22 12:39:30.000,2012-06-15 16:17:20.000,CPU2,4703995492_2011-11-10_14:00:02,58,NULL,NULL,0502999500_2012-01-21_15:28:20,3474,0230999782_2011-07-28_01:01:08,0,0142999875_2011-07-14_07:37:19)
INSERT into inven.CASH values(0663999346_2012-01-22_12:39:30,000000000000000000000000000000,2012-01-22 12:39:30.000,2012-06-15 16:17:20.000,CPU2,4703995492_2011-11-10_14:00:02,63,NULL,NULL,0502999500_2012-01-21_15:28:20,4388,0230999778_2011-07-28_01:00:01,0,0142999875_2011-07-14_07:37:19)
INSERT into inven.CASH values(0663999348_2012-01-22_12:39:31,000000000000000000000000000000,2012-01-22 12:39:31.000,2012-06-18 13:48:07.000,CPU2,4703995492_2011-11-10_14:00:02,67,NULL,NULL,0502999500_2012-01-21_15:28:20,4301,0230999776_2011-07-28_00:59:36,0,0142999875_2011-07-14_07:37:19)
INSERT into inven.CASH values(0663999350_2012-01-22_12:39:31,000000000000000000000000000000,2012-01-22 12:39:31.000,2012-06-18 13:48:07.000,CPU2,4703995492_2011-11-10_14:00:02,71,NULL,NULL,0502999500_2012-01-21_15:28:20,2977,0230999774_2011-07-28_00:59:12,0,0142999875_2011-07-14_07:37:19)
INSERT into inven.CASH values(0663999352_2012-01-22_12:39:31,000000000000000000000000000000,2012-01-22 12:39:31.000,2012-06-18 13:48:07.000,CPU2,4703995492_2011-11-10_14:00:02,59,NULL,NULL,0502999500_2012-01-21_15:28:20,118,0214999790_2011-07-14_13:18:01,0,0142999875_2011-07-14_07:37:19)
INSERT into inven.CASH values(0663999354_2012-01-22_12:39:31,000000000000000000000000000000,2012-01-22 12:39:31.000,2012-05-24 16:13:43.000,CPU2,4703995492_2011-11-10_14:00:02,17,NULL,NULL,0502999500_2012-01-21_15:28:20,2,0214999788_2011-07-14_13:17:16,0,0142999875_2011-07-14_07:37:19)
INSERT into inven.CASH values(0729999273_2012-06-13_14:22:43,000000000000000000000000000000,2012-06-13 14:22:43.000,2012-06-18 16:39:21.000,CPU3,4786995227_2012-01-30_15:19:43,14,NULL,NULL,1648998354_2011-08-29_05:55:18,162252,0465999599_2011-08-19_08:04:35,0,0142999865_2011-07-14_07:36:53)
INSERT into inven.CASH values(1150998852_2012-06-13_16:55:50,000000000000000000000000000000,2012-06-13 16:55:50.000,2012-06-18 17:10:24.000,CPU4,4953995079_2011-11-17_11:50:00,18,NULL,NULL,7353992652_2012-02-03_08:21:20,149151,0465999599_2011-08-19_08:04:35,0,0344999659_2011-08-09_00:38:15)
INSERT into inven.CASH values(1362998640_2012-03-21_10:57:24,000000000000000000000000000000,2012-03-21 10:57:24.000,2012-06-12 17:13:13.000,CPU5,4404995616_2011-11-03_17:10:30,72,NULL,NULL,0042999960_2011-07-14_01:38:29,23658,0465999599_2011-08-19_08:04:35,0,0001000002_2011-07-13_22:21:31)
What I wanted to do was pull back all rows where the LASTUPDATE < 2012-06-18 00:00:00.000
My Current query:
DECLARE @CurDat Date -- Declare Current Date
Select @CurDat = GETDATE()
Select * from inven.CASH
Where
CASH.LASTUPDATE < @CurDat
Assuming that today's date is 06/18/2012
Again, sorry for not including scripts and example data in first post.
July 2, 2012 at 2:23 pm
I had to make some changes to your code. One, I don't have your database. Two, I don't have your file groups. Three, I don't have your schemas. Four, I don't have your other tables for the foreign keys. Five, your insert statements needed fixing to work.
Once all that was done, I made one slight change to your code for query, changing GETDATE() to cast('20120618' as date) and your query worked. So, what is the problem?
Your code modified to work in my environment.
/****** Object: Table [dbo].[CASH] Script Date: 07/02/2012 12:20:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CASH](
[varchar](31) NOT NULL,
[HIDDEN] [varchar](31) NOT NULL,
[CREATION] [datetime] NOT NULL,
[LASTUPDATE] [datetime] NOT NULL,
[WORKSTATIONID] [varchar](64) NOT NULL,
[LUSERUPDATE] [varchar](31) NOT NULL,
[SEQNUMBER] [numeric](10, 0) NOT NULL,
[LUSER] [varchar](31) NULL,
[LZONE] [varchar](31) NULL,
[LSAFE] [varchar](31) NULL,
[NBDENOM] [numeric](10, 0) NOT NULL,
[LDENOM] [varchar](31) NOT NULL,
[TRANSFER] [numeric](10, 0) NULL,
[LCASHCENTRE] [varchar](31) NOT NULL,
CONSTRAINT [CASH_LINK_PK] PRIMARY KEY CLUSTERED
(
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80),
CONSTRAINT [CASH_UNIQUE_IDX] UNIQUE NONCLUSTERED
(
[LUSER] ASC,
[LZONE] ASC,
[LSAFE] ASC,
[LDENOM] ASC,
[TRANSFER] ASC,
[LCASHCENTRE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
)
GO
SET ANSI_PADDING OFF
GO
INSERT into dbo.CASH values('0661999352_2012-01-22_12:36:34','000000000000000000000000000000','2012-01-22 12:36:34.000','2012-06-13 16:43:33.000','CPU2','4703995492_2011-11-10_14:00:02',69,NULL,NULL,'4709995293_2011-11-10_14:15:43',12031,'0015000000_2011-07-13_23:15:59',0,'0142999875_2011-07-14_07:37:19');
INSERT into dbo.CASH values('0661999354_2012-01-22_12:36:34','000000000000000000000000000000','2012-01-22 12:36:34.000','2012-06-15 16:46:03.000','CPU2','4703995492_2011-11-10_14:00:02',94,NULL,NULL,'4709995293_2011-11-10_14:15:43',8182,'0014999998_2011-07-13_23:14:50',0,'0142999875_2011-07-14_07:37:19');
INSERT into dbo.CASH values('0663999344_2012-01-22_12:39:30','000000000000000000000000000000','2012-01-22 12:39:30.000','2012-06-15 16:17:20.000','CPU2','4703995492_2011-11-10_14:00:02',58,NULL,NULL,'0502999500_2012-01-21_15:28:20',3474,'0230999782_2011-07-28_01:01:08',0,'0142999875_2011-07-14_07:37:19');
INSERT into dbo.CASH values('0663999346_2012-01-22_12:39:30','000000000000000000000000000000','2012-01-22 12:39:30.000','2012-06-15 16:17:20.000','CPU2','4703995492_2011-11-10_14:00:02',63,NULL,NULL,'0502999500_2012-01-21_15:28:20',4388,'0230999778_2011-07-28_01:00:01',0,'0142999875_2011-07-14_07:37:19');
INSERT into dbo.CASH values('0663999348_2012-01-22_12:39:31','000000000000000000000000000000','2012-01-22 12:39:31.000','2012-06-18 13:48:07.000','CPU2','4703995492_2011-11-10_14:00:02',67,NULL,NULL,'0502999500_2012-01-21_15:28:20',4301,'0230999776_2011-07-28_00:59:36',0,'0142999875_2011-07-14_07:37:19');
INSERT into dbo.CASH values('0663999350_2012-01-22_12:39:31','000000000000000000000000000000','2012-01-22 12:39:31.000','2012-06-18 13:48:07.000','CPU2','4703995492_2011-11-10_14:00:02',71,NULL,NULL,'0502999500_2012-01-21_15:28:20',2977,'0230999774_2011-07-28_00:59:12',0,'0142999875_2011-07-14_07:37:19');
INSERT into dbo.CASH values('0663999352_2012-01-22_12:39:31','000000000000000000000000000000','2012-01-22 12:39:31.000','2012-06-18 13:48:07.000','CPU2','4703995492_2011-11-10_14:00:02',59,NULL,NULL,'0502999500_2012-01-21_15:28:20',118,'0214999790_2011-07-14_13:18:01',0,'0142999875_2011-07-14_07:37:19');
INSERT into dbo.CASH values('0663999354_2012-01-22_12:39:31','000000000000000000000000000000','2012-01-22 12:39:31.000','2012-05-24 16:13:43.000','CPU2','4703995492_2011-11-10_14:00:02',17,NULL,NULL,'0502999500_2012-01-21_15:28:20',2,'0214999788_2011-07-14_13:17:16',0,'0142999875_2011-07-14_07:37:19');
INSERT into dbo.CASH values('0729999273_2012-06-13_14:22:43','000000000000000000000000000000','2012-06-13 14:22:43.000','2012-06-18 16:39:21.000','CPU3','4786995227_2012-01-30_15:19:43',14,NULL,NULL,'1648998354_2011-08-29_05:55:18',162252,'0465999599_2011-08-19_08:04:35',0,'0142999865_2011-07-14_07:36:53');
INSERT into dbo.CASH values('1150998852_2012-06-13_16:55:50','000000000000000000000000000000','2012-06-13 16:55:50.000','2012-06-18 17:10:24.000','CPU4','4953995079_2011-11-17_11:50:00',18,NULL,NULL,'7353992652_2012-02-03_08:21:20',149151,'0465999599_2011-08-19_08:04:35',0,'0344999659_2011-08-09_00:38:15');
INSERT into dbo.CASH values('1362998640_2012-03-21_10:57:24','000000000000000000000000000000','2012-03-21 10:57:24.000','2012-06-12 17:13:13.000','CPU5','4404995616_2011-11-03_17:10:30',72,NULL,NULL,'0042999960_2011-07-14_01:38:29',23658,'0465999599_2011-08-19_08:04:35',0,'0001000002_2011-07-13_22:21:31');
--My Current query:
DECLARE @CurDat Date -- Declare Current Date
Select @CurDat = cast('20120618' as date);
Select * from dbo.CASH
Where
LASTUPDATE < @CurDat;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply