More date problems! - SQL Server Forums

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

Development Tools

Other Development Tools

More date problems!

Author

Topic

Danny__T
Starting Member

27 Posts

Posted - 2004-06-27 : 11:57:34

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

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-27 : 12:06:18

try
update news set news_date='20040523' WHERE news_id = 11

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Danny__T
Starting Member

27 Posts

Posted - 2004-06-27 : 12:43:44

ah ok that works a treat...

now i have to convert the format from dd/mm/yyyy in my asp page to yyyymmdd

if I have the date stored in a variable 'newsDate' how is best to convert? Becase that isn't one of the standard datetimeformat settings is it?

Thanks again!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-27 : 13:47:43

datepart("yyyy",now()) & right("00" & datepart("m",now()),2) & right("00" & datepart("d",now()),2)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-27 : 15:01:19

Or you could just CONVERT(VARCHAR,GETDATE(),112).

This will give you a full list.

SET NOCOUNT ON

DECLARE
@min INT,
@max INT,
@date DATETIME

SELECT
@min = 1,
@max = 131,
@date = GETDATE()

SELECT @date

WHILE @min <= @max
BEGIN

IF @min BETWEEN 15 AND 19
OR @min = 26
OR @min BETWEEN 27 AND 99
OR @min BETWEEN 115 AND 119
OR @min BETWEEN 122 AND 125
OR @min BETWEEN 127 AND 129
BEGIN
GOTO NEXT_LOOP
END

SELECT @min, CONVERT(VARCHAR,@date,@min)

NEXT_LOOP:

SELECT @min = @min + 1
END

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-27 : 15:03:50

Escept that this is formatting the date in asp to create the sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rolf
Starting Member

1 Post

Posted - 2005-03-31 : 03:21:13

try using

set dateformat dmy
or mdy / ymd etc

Rolf

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-31 : 09:26:09

is this entire thread for real or am I dreaming?

Don't format the date anywhere. Get the value from the user. Create a stored proc with parameters of the proper type. Set the parameters, call the stored proc, have the stored proc insert the data.

There is no formatting that needs to be done anywhere, except perhaps in your ASP page to ensure you have collected a valid date.

In addition, when you need to format any dates in ASP, use the ToString() method of any datetime variable which gives you about a million easy to use options to format any date exactly as you need.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources