can't insert dates in SQL Server 2000

[Ray at]

Perhaps that's because what you're actually converting is 31 divided by 7
divided by 2003 instead of the string '31/07/2003'.

Ray at home

--
Will trade ASP help for SQL Server help


"Nick" <ektorjr@hol.gr> wrote in message
news:355901c35544$26d7cd80$a001280a@phx.gbl...

> Hello
>
> Suppose many of you have faced this problem before. I try
> to insert a date form my ASP page in SQL Server 2000 (in
> a smalldatetime datatype), but this seems impossible.
>
> After receiveing many error messages i found that i had
> to use the Convert function in my query. Well, I am using:
>
> update games set game_date=CONVERT(smalldatetime,
> 31/07/2003)
>
> The query works fine, but it inserts the value 01/01/1900
> in the database. I also tried others formats of the date
> (07/31/2003 etc.) but nothing works.
>
> It would be gretaly appreciated if could have a code
> snippet with an insert statement for SQL Server from ASP
> contaning dates.
>
> Thanks in advance.
>
> Nick
>

[Aaron Bertrand - MVP]

> update games set game_date=CONVERT(smalldatetime,

> 31/07/2003)

Dates are strings, not numbers, at least in this context (storage is another
issue).

So, you need to use quotes.

Also, you should try and avoid regionally formatted dates, they introduce
ambiguity and potential errors / data corruption. Try:

UPDATE games SET game_date = '2003-07-31'

A

[Aaron Bertrand - MVP]

Actually, safer without the dashes

UPDATE games SET game_date = '20030731'

[Nikos Drandakis]

I used quotes at first, but I alwys received an error mesage. When i
tried this it worked. So I assumed that, within the convert function it
needed no quotes.

Nick


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!