Problems with date field inputing 01/01/1900

Question

Problems with date field inputing 01/01/1900

Viewing 4 posts - 1 through 5 (of 5 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 Old Hand Points: 322 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