Date Time format - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Date Time format

Author

Topic

JERICHO
Starting Member

18 Posts

Posted - 2003-03-10 : 06:59:08

hey guys i am facing a time format here in SQL Server 2k

i do a normal insert query

insert into date_tbl
(start_date)
values
(19/11/82)

but when i do my select statement in query analyzer, i see 1900-01-01 00.00.00 . But i only want the date not time and the format is wrong. Is there any way i can set the format to be standardise so every date i insert the format is the same.

drumm
Starting Member

14 Posts

Posted - 2003-03-10 : 07:04:00

Why not use the ISO standard YYYY-MM-DD HH:MM:SS? That should work every time. For example, your time format would be 1982-11-19 00:00:00.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-03-10 : 07:17:38

SmallDateTime and DateTime - SQL's 2 DAte data types, both carry time. So either live with that, or use CAST with style type to get what you want ( see BOL).

Alternatively, store data as text in char. (ugly, imho).

HTH

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-10 : 08:41:03

YYYY-MM-DD HH:MM:SS is also language dependent.
The iso format is yyyymmdd which will always be unambiguous.

datetimes are held as a decimal number with the integer part the date and decimal part the time.
To get a particular format in query analyser you will usually have to do an explicit convert.
SQL server is a datebase and gives rudimentery presentation capabilities.

To get the format you want

convert(varchar(8), dtefield, 3)

==========================================
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.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-10 : 08:58:23

jericho -

some people are missing the question, I think, but providing good info.

SQL is dividing 19 by 11 by 82 and inserting that into start_date.

Try putting a single quote around your date:

insert into date_tbl (start_date) values ('19/11/82')

But make sure that SQL is understanding that your date format is dd/mm/yy. It would be better, as nr states, to say:

insert into date_tbl (start_date) values ('19821911')

to avoid any confusion. But the main problem you are experiencing now is the lack of quote delimiters to your date in your instert statement.

- Jeff

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-03-10 : 19:02:35

you can use something like these
convert(char(10),getdate(),101)
or
convert(char(10),getdate(),121)

replace getdate() with ur date columnname

JERICHO
Starting Member

18 Posts

Posted - 2003-03-11 : 04:02:05

Hey guys thanks for helping me, but i still dun get it and i dun think i can do a casting or convert function within a insert statement right?

The problem i don't get is the 1900-01-01 cause it seems to do some minus or mutipling within while i insert the date with 19/11/82.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-03-11 : 05:57:50

SQL Server doesn't have date literals (unlike Access, for instance, where they are enclosed in # characters).
You have to use strings and either let it implicitly cast them into datetime type values or put the cast in explicitly.
Since there are two contradictory date formats of the form 'nn/nn/nnnn', this is a bad choice to let SQL Server cast implicitly. Idiotically, the format 'nnnn-nn-nn' can be interpreted by SQL Server either as 'yyyy-mm-dd' or 'yyyy-dd-mm' even though the latter format is not used anywhere, ever! Consequently, the only string date format that can be unambiguously converted into a datetime value without an explicit cast is 'yyyymmdd'.

SQL Server converts integers into dates treating them as a number of days after its date origin, which happens to be 1st Jan 1900. Consequently, doing
SELECT CAST(1900-01-01 AS datetime)
is taking the number 1900, subracting 1 from it twice and then converting the result, 1898 into a datetime, giving a result of 1905-03-14 00:00:00.000.
Similarly,
SELECT CAST(19/11/82 AS datetime)
is taking 19, dividing by 11 to give 1 and dividing that by 82 to give 0, then converting that into a datetime, giving the origin of 1900-01-01 00:00:00.000.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources