From yyyymm calculate the first and last day

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

From yyyymm calculate the first and last day

Author

Topic

mosquito1270
Starting Member

6 Posts

Posted - 2009-08-03 : 05:31:23

Hi,
I need to calculate the first and the last day of the mounth, from the date in this format "yyyymm"

Thank's for any suggestions!
Ciao

Paolo

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 05:38:13

[code]
declare @datestr varchar(10)

select @datestr = '200905'

select first_of_month = convert(datetime, @datestr + '01', 112)

select last_of_month = dateadd(month, 1, convert(datetime, @datestr + '01', 112)) - 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mosquito1270
Starting Member

6 Posts

Posted - 2009-08-03 : 05:52:16

Thank's,
but I'll return this error

(esp) "Error de conversión al convertir una cadena de caracteres a datetime."
(uk) "Convertion error to convert char string to datetime"

when I execute this comand

execute N_ESTR_COSTETOTA '200906','243561'

This is the code.

Thank's

-- Borro el procedimiento si existe
if exists (select name from sysobjects where name = 'N_ESTR_COSTETOTA' and type = 'P')
drop procedure N_ESTR_COSTETOTA
go

-- Creación del procedimiento
create procedure N_ESTR_COSTETOTA
-- Declaración de parametros que necesita.
-- Puede recibir: 'yyyymm'
@Fecha varchar(6),
@SerId varchar(20),
@debug integer=0
as
begin
SET NOCOUNT ON -- Para evitar el que desde el analizador aparezcan msjes del tipo 1 fila afectada
SET ANSI_WARNINGS OFF -- Para evitar mensajes "Advertencia: valor NULL eliminado por el agregado u otra operación SET."

------------------------------------------------------------------------------------------------------------
-- ##### PASO 0.- DEBUG y VARIABLES
------------------------------------------------------------------------------------------------------------
declare @debug_str varchar(8000)
declare @checkpoint datetime
declare @prefix varchar(100)

set @prefix=' N_ESTR_COSTETOTA '
set @checkpoint = getdate() -- inicializamos checkpoint
execute CHECK_POINT '', '', @debug, @prefix, 3

-- Variables para convertir @Fecha (varchar) a datetime
declare @fecha_desde datetime
declare @fecha_hasta datetime

select @fecha_desde = convert(datetime, @Fecha + '01', 112)
select @fecha_hasta = dateadd(month, 1, convert(datetime, @Fecha + '01', 112)) - 1

-- Imprimir los parametros que recibo, y los que son fechas, convertidos a datetime
set @debug_str='@Fecha = ' + @Fecha
execute CHECK_POINT @debug_str, '', @debug, @prefix, 2
set @debug_str='@fecha_desde = ' + @fecha_desde
execute CHECK_POINT @debug_str, '', @debug, @prefix, 2
set @debug_str='@fecha_hasta = ' + @fecha_hasta
execute CHECK_POINT @debug_str, '', @debug, @prefix, 2

end
go

grant execute on N_ESTR_COSTETOTA to public
go

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 06:06:01

I don't have problem with the following codes (copied from your stored procedure).


DECLARE @Fecha varchar(6)

SELECT	@Fecha = '200906'
SELECT  fecha_desde = CONVERT(datetime, @Fecha + '01', 112)
SELECT  fecha_hasta = DATEADD(MONTH, 1, CONVERT(datetime, @Fecha + '01', 112)) - 1

/*
fecha_desde                                            
------------------------------------------------------ 
2009-06-01 00:00:00.000

(1 row(s) affected)

fecha_hasta                                            
------------------------------------------------------ 
2009-06-30 00:00:00.000

(1 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 06:08:04

The error should be coming from here

quote:
set @debug_str='@fecha_desde = ' + @fecha_desde
set @debug_str='@fecha_hasta = ' + @fecha_hasta

You are concatenating string with datetime. Convert the datetime to string first before concatenate


set @debug_str='@fecha_desde = ' + convert(varchar(10), @fecha_desde, 112)

KH
[spoiler]Time is always against us[/spoiler]

mosquito1270
Starting Member

6 Posts

Posted - 2009-08-03 : 06:16:17

quote:
Originally posted by khtan

The error should be coming from here
quote:
set @debug_str='@fecha_desde = ' + @fecha_desde
set @debug_str='@fecha_hasta = ' + @fecha_hasta

You are concatenating string with datetime. Convert the datetime to string first before concatenate

set @debug_str='@fecha_desde = ' + convert(varchar(10), @fecha_desde, 112)

KH
[spoiler]Time is always against us[/spoiler]

Thank's.
The error is as you write.

sorry for the mistake.

Ciao
Paolo

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 06:17:55

no problem

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources