January 18, 2010 at 3:09 pm
Good afternoon, Im a newbie about programming, but specifically i tried thia code in (sqlserver 200) in order to know how many records were loaded yesterday
set quoted_identifier off
go
set ansi_nulls off
go
DECLARE @P_YESTERDAY CHAR(8)
SET @P_YESTERDAY = CONVERT(CHAR(8),GETDATE()-1,112)
select count(1), FECHA
from TableA a
LEFT JOIN
DM1_DIMENSIONES..DIM_CALENDARIO b
ON a.COD_DIM_CALENDARIO_FEC_TASACION = b.COD_DIM_CALENDARIO
WHERE b.FECHA = "'+@P_YESTERDAY+'"
group by FECHA
EXEC SP_EXECUTESQL @V_CADENA
GO
set quoted_identifier off
go
set ansi_nulls on
go
/the field COD_DIM_CALENDARIO_FEC_TASACION is datetime type, and i'll appreciate your help
January 18, 2010 at 3:22 pm
If I understand you correctly these 2 fields are both datetime
ON a.COD_DIM_CALENDARIO_FEC_TASACION = b.COD_DIM_CALENDARIO
is that correct?
If so you, are making a comparision up to and including milliseconds ...
have you attempted the comparision using just the date that is setting time to 00:00:000?
If you want to test that the use:
SELECT dateadd(dd, datediff(dd, 0, getdate()), 0) which results in: 2010-01-18 00:00:00.000 Use for each field (COD_DIM_CALENDARIO_FEC_TASACION and COD_DIM_CALENDARIO) -substituting for getdate() in the above T-SQL
This will also get you the previous date:
select dateadd(dd, datediff(dd, 0, Getdate()) - 1, 0) -- Beginning of previous day
January 18, 2010 at 3:51 pm
bots fileds are defined as datetime(8)
January 18, 2010 at 4:11 pm
Since both are datetime in a equality statement (=) you will want to compare them without considering time ... that is using just the date.
Have you attempted that, and did it help?
January 19, 2010 at 12:36 pm
Verifyng the data supplied yesterday, ive got the say something.
Both fiels a.COD_DIM_CALENDARIO_FEC_TASACION and b.COD_DIM_CALENDARIO are int (integer) and the field FECHA is datetime and the format for this field is YYYY-MM-DD 00:00:00.000
According to your code,i modified the query but it yielded the same error , im not sure if i forgot to set the var to the result of the select command, the new query is this:
set quoted_identifier off
go
set ansi_nulls off
go
DECLARE @P_FECHADIAYER datetime
select dateadd(dd, datediff(dd, 0, Getdate()) - 01, 0)
select count(1), FECHA
from hechos_trafico_llamadas_entrante_otros_201001 a
LEFT JOIN
DM1_DIMENSIONES..DIM_CALENDARIO b
ON a.COD_DIM_CALENDARIO_FEC_TASACION = b.COD_DIM_CALENDARIO
WHERE b.FECHA = "'+@P_FECHADIAYER+'"
group by FECHA
set quoted_identifier off
go
set ansi_nulls on
go
Thanks for your Help
January 19, 2010 at 2:50 pm
A slight modification of your code from:
DECLARE @P_FECHADIAYER datetime
select dateadd(dd, datediff(dd, 0, Getdate()) - 01, 0)
To:
SET @P_FECHADIAYER = dateadd(dd, datediff(dd, 0, Getdate()) - 01, 0)
--
--following just to display result to verify proper format
--
SELECT @P_FECHADIAYER
--which results in @P_FECHADIAYER becoming 2010-01-18 00:00:00.000
--which is the same as your format of YYYY-MM-DD 00:00:00.000
January 21, 2010 at 12:08 pm
Good afternoon
According to the sentences you suggested me in the previous post aer working fine, but combined with the nes set of ommands ive got the same error:
select count(1), FECHA
from hechos_trafico_llamadas_entrante_otros_201001 a
LEFT JOIN
DM1_DIMENSIONES..DIM_CALENDARIO b
ON a.COD_DIM_CALENDARIO_FEC_TASACION = b.COD_DIM_CALENDARIO
WHERE b.FECHA = "'+@P_FECHADIAYER+'"
group by FECHA
But explicitely i typed the date in the sentence below:
WHERE b.FECHA = '2010-01-18 00:00:00.000'
In the above sentence Ive got the results but it took too long (almost and hour)
Briefly My intention is to obtain an input parameter with the var +@P_FECHADIAYER i.e. 20100118 (with this format) and before implmenting a SP (store procedure) i need thas the previus set of commands work properly.
Ill appreciate your help
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply