Datetime data types - 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 2005 Forums

Transact-SQL (2005)

Datetime data types

Author

Topic

SQL_Rookie
Starting Member

32 Posts

Posted - 2009-08-06 : 09:42:23

OK I have a basic question I think...

I have a table that stores datetime. Now does it matter what the datetime format is ? I mean when I insert the data into the column does it matter what format its in or does sql takes the date and uses the internal datetime setting (US, British, etc...) and stores it.

Reason I'm asking is when the compare is done in a sql statement like

select * from table where date_column1 = date_column2

do I need to convert the datetime columns to the same format like

select * from table where convert(datetime, date_column1, 101) = convert(datetime, date_column2, 101)

I think I don't need to convert but I wanted to make sure.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-06 : 09:47:05

quote:
Now does it matter what the datetime format is ? I mean when I insert the data into the column does it matter what format its in or does sql takes the date and uses the internal datetime setting (US, British, etc...) and stores it.

Datetime datatype store the date and time it it's internal representation and it is not MM/DD/YYYY or DD/MM/YYYY. When you assign a datetime to it, always use ISO format (YYYYMMDD)to avoid ambiguity.

quote:
select * from table where date_column1 = date_column2

Assuming that the date_column1 and date_column2 are in datetime data type then this is fine.

quote:
do I need to convert the datetime columns to the same format like

select * from table where convert(datetime, date_column1, 101) = convert(datetime, date_column2, 101)

You don't have to convert and this will only slow down the query

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 09:54:21

No. The internal storage format is binary(8).

However the time part is evaluated too, so if you only are interested in getting records from same date, try this

select * from table where datediff(day, date_column1, date_column2) = 0

N 56°04'39.26"
E 12°55'05.63"

SQL_Rookie
Starting Member

32 Posts

Posted - 2009-08-06 : 10:13:48

the time portion is set to midnight 00:00:00 or when the records are inserted there is no time portion so it stores 00:00:00.

What if I'm comparing tableA.datetime_col to tableB.string_datetime

I would just need to convert the string datetime to a datetime data type using the convert function with the style of the region of date that was store in
like

for US dates store as string on tableB col I would use
select * from
tableA, tableB
where tableA.col = convert(datetime, tableB.col, 101)
and tableB.country = 'USA'

for Europe or Bristish dates store as string on tableB col I would use
select * from
tableA, tableB
where tableA.col = convert(datetime, tableB.col, 103)
and tableB.country = 'Europe'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 10:42:41

Why do you store dates as strings? That is the most common mistake developers do.

A datetime value is stored in an internal binary value.
What you see on screen is just a character representation!

/*select * from
tableA, tableB 
where tableA.col = convert(datetime, tableB.col, 101)
and tableB.country = 'USA'

--for Europe or Bristish dates store as string on tableB col I would use
select * from
tableA, tableB 
where tableA.col = convert(datetime, tableB.col, CASE WHEN 1 = 1 THEN 103 ELSE 102 END)
and tableB.country = 'Europe'
*/

DECLARE	@Sample TABLE
	(
		Country VARCHAR(20),
		Col VARCHAR(20)
	)

INSERT	@Sample
SELECT	'USA', '12/31/2008' UNION ALL
SELECT	'Europe', '31/12/2008'

DECLARE	@Stage TABLE
	(
		Col DATETIME
	)

INSERT	@Stage
SELECT	'20081231'

SELECT		s.Country,
		s.Col,
		w.Col
FROM		@Sample AS s
INNER JOIN	@Stage AS w ON w.Col = CONVERT(DATETIME, s.Col, CASE s.Country WHEN 'USA' THEN 101 WHEN 'Europe' THEN 103 ELSE 112 END)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources