setting default datetime

Question

Post reply

setting default datetime

Dinuj Nath-203580

SSC Veteran

Points: 207
More actions
June 4, 2005 at 12:41 am

#164001

Hi,
I tried to set a default datetime value for a column. I set '2005-1-1', but on inserting new rows the date is shown as 25/06/1905.
What's wrong?
Thanks and Regards,
Dinuj Nath

Viewing 10 posts - 1 through 9 (of 9 total)

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

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 1

If you are setting the default date in enterprise manager try using ("2005-1-1").

HTH Mike

Dinuj Nath-203580 SSC Veteran Points: 207 More actions · Answer 2

Thanks for replying but, that's what I have done.

Thanks and Regards,

Dinuj Nath

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 3

Sorry but I can not recreate your problem.

HTH Mike

CREATE TABLE #TEST

(

DT datetime default('2005/1/20'),

val int

)

insert into #Test(Val)Values(1)

insert into #Test Values(cast('1/21/99' as datetime),2)

select * from #Test

DROP TABLE #Test

/* returns

2005-01-20 00:00:00.000 1

1999-01-21 00:00:00.000 2

*/

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 4

Try '20050120'

This is the ISO standard way of representing a date as string.

SQLServer takes this format independant of the datetime configuration of the computer.

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

Alternately - you can "set dateformat":

SET DATEFORMAT ydm

GO

DECLARE @datevar datetime

SET @datevar = '2005-1-1'

SELECT @datevar

GO

**ASCII stupid question, get a stupid ANSI !!!**

Dinuj Nath-203580 SSC Veteran Points: 207 More actions · Answer 6

Thanks folks, the ISO standard worked

Thanks and Regards,

Dinuj Nath

Ian Yates SSCoach Points: 19738 More actions · Answer 7

I think Michael's point is valid - if you were using EM, make sure that you let it know it is a string as in

"2005-1-1", NOT 2005-1-1 as it would be 2003, which (I am guessing) may well be 2003 days past 30-12-1899 which is date #0 in sql and many other environments. You would get the same by not having quotes in your SQL statement in query analyser.

Cheers!

PhilPacha Hall of Fame Points: 3645 More actions · Answer 8

Date "0" for MS SQL Server is Jan. 1, 1900,

NOT Dec. 30, 1899.

Ian Yates SSCoach Points: 19738 More actions · Answer 9

True, it does appear the case in query analyser...

My 1899-12-30 answer came from me using Delphi for 95% of my development - if I retrieve a 0 datetime value from SQL Server that is the date that is retrieved oddly enough.

Anyone know why this is?