Problem with handling dates

hi all
I have a stored procedure that gets date from a particular field using the DATEPART function. However, somewhere in between it has to do an update which is why it adds hours, days or months to the function. Here is the code
SELECT @.var_NewNextRunDate=(CAST(DATEPART(YYYY,@.DT) AS CHAR(4)) + '/'
+ RIGHT(CAST(100+DATEPART(MM,@.DT) AS CHAR(3)),2) + '/'
+ RIGHT(CAST(100+DATEPART(DD,@.DT) AS CHAR(3)),2) + ' '
+ RIGHT(CAST(100+DATEPART(HH,@.DT) + @.var_Frequency AS CHAR(3)),2) + ':'
+ RIGHT(CAST(DATEPART(MI,@.DT) AS CHAR(2)),2) + ':'
+ RIGHT(CAST(DATEPART(SS,@.DT) AS CHAR(2)),2)+ ':'
+ RIGHT(CAST(DATEPART(MS,@.DT) AS CHAR(3)),3) )
Now the problem is that when there a change in day the hours get stuck because it simply adds the hours so if it is 23:40 and it adds 3 it becomes 26:40 which makes no sense. Can someone please help me find a solution to this? I have the same situation with days, and months as well.
Thank you.you should do all data/time manipulations on a date/time datatypes not chars.
if you are extracting a portion of an existing date/time which results in a char datatype, you should cast it back to date/time before you start manipulating it.|||Although this is an old thread I just thought I'd add what I did. I just used the dateadd function to resolve the issue. It worked just perfect.

Problem with GROUP BY/COMPUTE : error message 8120

Hi,
I have this query ( it is Ok with Sybase SQLServer)
select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
,'Anne'=datepart(yy,DPSTVOI),'Priode'=
CPST,'Nombre'=count(NVOI)
from HREH3M
group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
compute sum(count(NVOI)) by CPST,NVOI,datepart(yy,DPSTVOI)
I want to migrate it under MS SQLServer 2000, but I have this error message
:
Serveur : Msg 8120, Niveau 16, tat 1, Ligne 1
Column 'HREH3M.DPSTVOI' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
If I delete the last line, the query is Ok :
select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
,'Anne'=datepart(yy,DPSTVOI),'Priode'=
CPST,'Nombre'=count(NVOI)
from HREH3M
group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
The problem is with COMPUTE claude, but I can't understand why.
please help me to solve this.
thanks in advance
regards
LaurentWhat kind of error or warning appears when you drop that line? Apparently
syntax for COMPUTE is fine.
"Laurent CLAUDEL" wrote:

> Hi,
> I have this query ( it is Ok with Sybase SQLServer)
> select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
> ,'Année'=datepart(yy,DPSTVOI),'Période
'=CPST,'Nombre'=count(NVOI)
> from HREH3M
> group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> compute sum(count(NVOI)) by CPST,NVOI,datepart(yy,DPSTVOI)
>
> I want to migrate it under MS SQLServer 2000, but I have this error messag
e
> :
> Serveur : Msg 8120, Niveau 16, état 1, Ligne 1
> Column 'HREH3M.DPSTVOI' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> If I delete the last line, the query is Ok :
> select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
> ,'Année'=datepart(yy,DPSTVOI),'Période
'=CPST,'Nombre'=count(NVOI)
> from HREH3M
> group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> The problem is with COMPUTE claude, but I can't understand why.
> please help me to solve this.
> thanks in advance
> regards
> Laurent
>
>|||if I drop the last line (COMPUTE), there is no more error.
"Enric" <Enric@.discussions.microsoft.com> a crit dans le message de news:
B27D7A22-9880-4A31-9335-D0C77C2DED83@.microsoft.com...
> What kind of error or warning appears when you drop that line? Apparently
> syntax for COMPUTE is fine.
> "Laurent CLAUDEL" wrote:
>|||But I want a sum by Year, so i have to keep the COMPUTE clause
"Laurent CLAUDEL" <laurent.claudel@.steria.com> a crit dans le message de
news: OhOqg8Y1FHA.1108@.TK2MSFTNGP14.phx.gbl...
> if I drop the last line (COMPUTE), there is no more error.
> "Enric" <Enric@.discussions.microsoft.com> a crit dans le message de news:
> B27D7A22-9880-4A31-9335-D0C77C2DED83@.microsoft.com...
>|||I suggest you don't use COMPUTE / COMPUTE BY unless it's essential to
maintain Sybase compatibility. COMPUTE is legacy stuff that was
deprecated long ago. Take a look at CUBE / ROLLUP in Books Online -
it's a much more powerful feature.
David Portas
SQL Server MVP
--sql

problem with datepart ....

hello
i have a table with date entries of type string ...tries of type varchar li
ke
01.01.2006
.....
01.02.2006
.....
28.02.2006
print DATEPART(dw,'01.02.2006') ->2
print DATEPART(dw,'14.02.2006') ->
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
i think the problems are because on server are default german and the othe
are english.
I did not want to change all kind of scrips - Is the any possibility to add
a command in the first line to change this ...
best reagrds
what can i do?
print DATEPART(dw,'14.02.2006')Try:
print DATEPART(dw,convert (datetime, '14.02.2006', 104))
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
hello
i have a table with date entries of type string ...tries of type varchar
like
01.01.2006
.....
01.02.2006
.....
28.02.2006
print DATEPART(dw,'01.02.2006') ->2
print DATEPART(dw,'14.02.2006') ->
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
i think the problems are because on server are default german and the othe
are english.
I did not want to change all kind of scrips - Is the any possibility to add
a command in the first line to change this ...
best reagrds
what can i do?
print DATEPART(dw,'14.02.2006')|||try this
SET DATEFORMAT DMY
SELECT DATEPART(dw,'14.02.2006')
http://sqlservercode.blogspot.com/|||Xavier,
I suggest you look up (& make use of) the
SET DATEFORMAT
statement in Books Online.
Robert
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
> hello
> i have a table with date entries of type string ...tries of type varchar
> like
> 01.01.2006
> .....
> 01.02.2006
> .....
> 28.02.2006
>
> print DATEPART(dw,'01.02.2006') ->2
> print DATEPART(dw,'14.02.2006') ->
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> i think the problems are because on server are default german and the othe
> are english.
> I did not want to change all kind of scrips - Is the any possibility to
> add
> a command in the first line to change this ...
> best reagrds
> what can i do?
> print DATEPART(dw,'14.02.2006')
>|||Try using CONVERT with the proper format.
print DATEPART(dw,convert(datetime, '14.02.2006', 104))|||thanks
"Tom Moreau" wrote:

> Try:
> print DATEPART(dw,convert (datetime, '14.02.2006', 104))
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:624253D1-F280-433B-86DB-EAF822D6E6C3@.microsoft.com...
> hello
> i have a table with date entries of type string ...tries of type varchar
> like
> 01.01.2006
> ......
> 01.02.2006
> ......
> 28.02.2006
>
> print DATEPART(dw,'01.02.2006') ->2
> print DATEPART(dw,'14.02.2006') ->
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> i think the problems are because on server are default german and the othe
> are english.
> I did not want to change all kind of scrips - Is the any possibility to ad
d
> a command in the first line to change this ...
> best reagrds
> what can i do?
> print DATEPART(dw,'14.02.2006')
>
>|||thanks