strange problem with dates

Sometimes I get error : "The conversion of char data type to smalldatetime
data type resulted in an out-of-range smalldatetime value. "
If I look on SQL there is ok example with error - insert into calendar
(tema, pol_1, dodal, date_from, date_to, user_id, status) Values ('test' ,
'test' , 1, '2005-08-09 12:00:00', '2005-08-15 12:00:00', 2, 0 )

Example no error - insert into calendar (tema, pol_1, dodal, date_from,
date_to, user_id, status) Values ('test' , 'test' , 1, '2005-01-01
12:00:00', '2005-01-01 16:00:00', 1, 0 )
What is bad?


Regards,

re: strange problem with dates

You probably have a problem with dateformat. It is probably in a year, day,
month format and there isn't 15 months in a year :).

try this:

insert into calendar
tema, pol_1, dodal, date_from, date_to, user_id, status) Values ('test' ,
'test' , 1, '2005-09-08 12:00:00', '2005-15-08 12:00:00', 2, 0 )

or setting the format on and then inserting:


SET DATEFORMAT ymd

insert into calendar
(tema, pol_1, dodal, date_from, date_to, user_id, status)
Values ('test' , 'test' , 1, '2005-08-09 12:00:00', '2005-08-15 12:00:00',
2, 0 )




MC


"Zibi" <nospam@won.com> wrote in message
news:datfo0$bn3$1@nemesis.news.tpi.pl...[color=blue]
> Sometimes I get error : "The conversion of char data type to smalldatetime
> data type resulted in an out-of-range smalldatetime value. "
> If I look on SQL there is ok example with error - insert into calendar
> (tema, pol_1, dodal, date_from, date_to, user_id, status) Values ('test' ,
> 'test' , 1, '2005-08-09 12:00:00', '2005-08-15 12:00:00', 2, 0 )
>
> Example no error - insert into calendar (tema, pol_1, dodal, date_from,
> date_to, user_id, status) Values ('test' , 'test' , 1, '2005-01-01
> 12:00:00', '2005-01-01 16:00:00', 1, 0 )
> What is bad?
>
>
> Regards,
>
>[/color]

re: strange problem with dates

U¿ytkownik "MC" <marko_culo#@#yahoo#.#com#> napisa³ w wiadomo¶ci
news:datgu4$c8c$1@magcargo.vodatel.hr...
[color=blue]
>
> or setting the format on and then inserting:
>
>[/color]
Thx. I try to do it in this way:

date_from = FormatDateTime(Request("year_from") & "-" &
Request("month_from") & "-" & Request("day_from") &" "&
Request("hour_from") &":"& Request("min_from"))

re: strange problem with dates

Zibi (nospam@won.com) writes:[color=blue]
> Thx. I try to do it in this way:
>
> date_from = FormatDateTime(Request("year_from") & "-" &
> Request("month_from") & "-" & Request("day_from") &" "&
> Request("hour_from") &":"& Request("min_from"))[/color]

So this is where the real problem is. It appears that you build
SQL statements in their entirety in the client, and then pass
these to SQL Server.

That is now what you should do. You should use parameterised statements.
Then the client API translates the date value according to the regional
settings, and passes them to SQL Server as binary values that cannot
be misunderstood.

If you absolutely insist on sending down date literals, then use
the format YYYYMMDD which can never be interpreted in any other way
in SQL Server.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: strange problem with dates

Uzytkownik "Erland Sommarskog" <esquel@sommarskog.se> napisal w wiadomosci
news:Xns9690F3A0319B4Yazorman@127.0.0.1...
[color=blue]
>
> So this is where the real problem is. It appears that you build
> SQL statements in their entirety in the client, and then pass
> these to SQL Server.
>
> That is now what you should do. You should use parameterised statements.
> Then the client API translates the date value according to the regional
> settings, and passes them to SQL Server as binary values that cannot
> be misunderstood.
>
> If you absolutely insist on sending down date literals, then use
> the format YYYYMMDD which can never be interpreted in any other way
> in SQL Server.
>[/color]
Thanks for your comments, I like to have problem with dates I must learn
more about it