Problems with date field inputing 01/01/1900

Question

Problems with date field inputing 01/01/1900

orkie

Valued Member

Points: 53
More actions
October 19, 2004 at 4:32 am

#102240

Hello,
Whenever i try to run the following SQL the date is always not what's input but 01/01/1900,
insert into t_postCard(Source, toName, toEmail, fromName, fromEmail, message, readRecept, flash, dateSent, pass) values('postCard1', 'dfsd', 'sdgfgfsd@fdgd.com', 'fdsag', 'dgfd@dfga.com', 'fsfd', 0, 1, 19/10/2004 11:14:43, 7824)

can anyone offer any help?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I would try putting single quotes around your date/time. The system is probably interpreting your statement mathematically and not recognizing it as a date/time.

Good Hunting!

AJ Ahrens

webmaster@kritter.net

SQLBill SSC Guru Points: 51440 More actions · Answer 2

I would do a AJ suggests, but the reason for it is that when a datetime value is being inputted, it's being inputted as a string and SQL Server implicitly converts it to DATETIME datatype.

Also, start your insert with this:

SET DATEFORMAT dmy

That will ensure that SQL Server interprets the date in the proper format.

-SQLBill

mayowa omirinlewo Grasshopper Points: 14 More actions · Answer 3

SET DATEFORMAT dmy

is an option.

You can as well change your date format to ANSI

i.e. 19/10/2004 becomes '20041019'

SQL server converts this to date irrespective of the date format at database end.

Yuvraj Powar SSC Veteran Points: 272 More actions · Answer 4

Hi Orkei,

I think whereever u got the value as '01/01/1900'. you might have inserted blank value('') in database.When u try to insert blank value ('') in a column of Data type Date it will store '01/01/1900' in that column .To avoid this replace Blank '' with NULL.

For conversion use case statement or replace statement.

Thanks

Yuvraj