Date problems (again!)

I am trying to use asp to add/edit dates in a sql server db.

I can add a date fine using: -

INSERT INTO news(news_title, news_date, news_intro, news_text)
VALUES('The title of the news',
'25/06/2004',
'This is the introduction to the news article',
'this is the text for the news')

however when i try to update using: -

update news set news_date='23/05/2004' WHERE news_id = 11

I get the following error: -

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.
The statement has been terminated.

the date column is of the datatype datetime
I also have the same problem if i use the same sql directly in query analyzer

Any help HUGELY appreciated, cheers

Dan

OK so it works if I use
update news set news_date='20040523' WHERE news_id = 11

so how is best to convert my date variable 'newsDate' from dd/mm/yyyy to yyyymmdd in ASP?

I have looked at the datetimeformat function but that is limited to 4 set formats none of which are what i need.

Thnaks

Hello danny,this is my solution for u, but sorry i don't know to much in asp, this is the vb version

Private Sub Update_news()
dim rs_update as adoDB.recordset
set rs_update = new adoDB.recordset
rs_update.open "UPDATE NEWS SET NEWS_DATE = '" & FORMAT(newsDate, "YYYY - MM - DD") & "' WHERE news_id = 11", con, adOpenKeyset
set rs_update = nothing

end sub


Best Regards

Tonny

You may also just want to use the FormatDateTime function like so:

update news set news_date='" & FormatDateTime("23/05/2004",2) & "' WHERE news_id = 11

This way it will guarantee that it is an acceptable time format.

I'm not meaning to say that your way is wrong Tonny, but I have found when using ASP, SQL seems to have a problem when just using FORMAT with date data types.