Format Datetime type in column

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

SQL Server 2005 Forums

Transact-SQL (2005)

Format Datetime type in column

Author

Topic

duongthaiha
Starting Member

2 Posts

Posted - 2007-09-10 : 08:09:13

Hi
Can you please help me on this matter please?

I am trying to input UK(for example: 25/09/2007 ) format datetime in sql server. Is there any change that I can format my column to UK datetime. At the moment there is in US format(09/25/2007).

I have a table, one of the column has type datetime.

I use the insert command and the data i want to into is '25/09/2007'

It come back with error that the date is out of range. I change that to 09/09/2007 then it work.
The currently date and time format is MM/DD/YYYY.
I don't know how can i define the format to be DD/MM/YYYY

Thank you very much for your help
i really do appreciate that.

Best regards

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-10 : 09:55:21

Don't use either format; use the universal YYYY-MM-DD format:

insert into yourtable (yourdate) values ('2007-09-24')

Better yet, don't use date literals converted from strings at all. There is a function available here: http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
that let's you build dates passing in a year, month and day parameter, like this:

insert into yourtable (yourdate) values (dbo.Date(2007,09,24))

That way no converts and no date formats are used -- it just passes in a value.

It is not recommend to use implicit conversions of dates that uses a format that may vary -- i.e., m/d/y or d/m/y. Even if the server is set to use one or the other by default, it still can lead to confusion or complications.

- Jeff
http://weblogs.sqlteam.com/JeffS

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-10 : 22:12:31

Or use 'set dateformat' to specify display format.

Kristen
Test

22859 Posts

Posted - 2007-09-11 : 01:47:26

"Don't use either format; use the universal YYYY-MM-DD format:

insert into yourtable (yourdate) values ('2007-09-24')"

Being pedantic Jeff you need to leave out the hyphens "-"

I have no idea how one of our clients servers is set up, but this is what I get:

SELECT CONVERT(Datetime, '2007-24-09')
gives 2007-09-24 00:00:00.000

SELECT CONVERT(Datetime, '20070924')
gives 2007-09-24 00:00:00.000

SELECT CONVERT(Datetime, '2007-09-24')
gives "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"

Kristen

duongthaiha
Starting Member

2 Posts

Posted - 2007-09-11 : 03:58:01

Hi
It's happen to me the same as well.
anyone has a clue please?

Kristen
Test

22859 Posts

Posted - 2007-09-11 : 05:32:18

"anyone has a clue please?"

The advice above is: present "text" dates in the format 'YYYYMMDD'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-11 : 05:39:17

<<
SELECT CONVERT(Datetime, '2007-09-24')
gives "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"
>>

If the dateformat of the server is ydm then you will get that error

<
Being pedantic Jeff you need to leave out the hyphens "-"
>>

Well. It is also strange that VB6's ISDATE() function doesnt treat it as date if the date value is expressed in YYYYMMDD format. But it recognises other values like YYYY-MM-DD,YYYY/MM/DD,YYYY.MM.DD,etc

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources