what data type to choose? - 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

General SQL Server Forums

New to SQL Server Programming

what data type to choose?

Author

Topic

allan8989
Starting Member

6 Posts

Posted - 2008-07-08 : 18:11:53

i need create a column to hold date, just like 9/10/2008, not time involved. what data type should i choose? and if a column already has date like 9/10/2008 11:34:12PM how can i filter out the time? thanks in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-08 : 18:13:55

use datetime datatype.
best to use
where myDateColumns between '2008-07-08 00:00:00' and '2008-07-08 23:59:59'
for searches.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

allan8989
Starting Member

6 Posts

Posted - 2008-07-09 : 14:59:06

thanks, but it does not solve my problem. i want to save date just like month/day/year, no time attached. but if ie use datetime or smalldatetime, time automatically attached, even i don't type it. i know i can use char(10) to hold such date data but that's not good for date calculation. or if i have to use this with time then later how can i just display date without time displayed?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-09 : 15:01:25

there's no way to get rid of the time part. live with it

you can of course store all dates with 00:00:00.000 time part that will simplyfy your calculations.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

allan8989
Starting Member

6 Posts

Posted - 2008-07-09 : 15:18:36

if we really can't get rid of it then
1) microsoft sucks!!!
2) i think i can use Left(myDateCol, 11) as Date .... but it still sucks cuz it gives me format like Jan 12 2008

anyway thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-09 : 15:29:42

1) no it doesn't
2) this will give you varchar

what exactly is your problem that you really mustn't have time involved?

time can be easily striped out in the presentation layer.

of course you can always wait for SS 2k8 that will have separate date and time datatypes.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-09 : 15:39:12

U can use:

CONVERT(varchar, your_date_field, 101)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-09 : 15:50:18

this converts the datatime to varchar and it disables use of indexes on that column and proper calcualtions.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-09 : 15:51:01

There is no crime in not understanding. Hence, why there is a "New to SQL Server" forum. However, what is a crime is not heeding the advice of people that are SQL Professionals and live and breathe sql daily. If you are running a transactional system and do not store your dates as a DateTime (or possibly SmallDateTime) then you are doing yourself and your company a great disservice. The one exception to this is in data warehousing. Now, if you need further explanation or some examples, let us know and we can help you out. If you already have it in your head that you want to store a date as a VARCHAR, I would implore you to reconsider and not make a rash decision in ignorance.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-09 : 16:12:06

You can read more about how to use SQL Server datetimes here:
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

This shows how to remove the time from a datetime value:

select
	MyDate,
	DateOnly = dateadd(day,datediff(day,0,Mydate),0)
from
	( -- Test Data
	select Mydate = getdate()
	) a


Results:
MyDate                   DateOnly
-----------------------  -----------------------
2008-07-09 16:08:04.897  2008-07-09 00:00:00.000

CODO ERGO SUM

allan8989
Starting Member

6 Posts

Posted - 2008-07-09 : 17:25:16

pretty cool, michael. would you please tell me what's the use of "a" at the end for? thanks.

allan8989
Starting Member

6 Posts

Posted - 2008-07-09 : 17:33:40

you can keep the index of date by using "order by" the original date column.

select your_date_field, CONVERT(varchar, your_date_field, 101) as Converted_Date
from your_table
order by your_date_field

here suppose your_date_field is datetime data type based.
thank you cvipin.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-09 : 17:40:27

quote:
Originally posted by allan8989

pretty cool, michael. would you please tell me what's the use of "a" at the end for? thanks.

The "a" is the alias for the derived table.

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-07-09 : 18:41:45

also, SQL Server 2008 has a type for dates only, without time. also there's a time type, without the date.

so you'll get your wish when you upgrade :)

elsasoft.org

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-10 : 05:38:59

Personally, the only instance where I am not happy to have time in the database is during the presentation layer - when I am presenting a date. in this case, I will convert the datetime to a universal format (dd MMM yyyy) instead of dd/mm/yyyy or mm/dd/yyyy to avoid cultural misunderstanding. i.e. convert(varchar, dateName, 106) as dateName will allow you display the date without time.

but when keeping logs or date of purchase, or date of entry, the time is always helpful for me to monitor things to find out what time something is being done etc. :) And yes, SQL Server 2008 has a Date datatype.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-10 : 09:42:07

quote:
Originally posted by EugeneLim11

Personally, the only instance where I am not happy to have time in the database is during the presentation layer - when I am presenting a date. in this case, I will convert the datetime to a universal format (dd MMM yyyy) instead of dd/mm/yyyy or mm/dd/yyyy to avoid cultural misunderstanding. i.e. convert(varchar, dateName, 106) as dateName will allow you display the date without time.

but when keeping logs or date of purchase, or date of entry, the time is always helpful for me to monitor things to find out what time something is being done etc. :) And yes, SQL Server 2008 has a Date datatype.

Format 106 is not culturally neutral. It depends on the language setting.

It is better to return the date to the client application as a datetime, and let the client do the date formatting.


set language us_english
select convert(varchar,getdate(), 106)
set language french
select convert(varchar,getdate(), 106)
set language polish
select convert(varchar,getdate(), 106)
set language finnish
select convert(varchar,getdate(), 106)

Results:
Changed language setting to us_english.
                               
------------------------------ 
10 Jul 2008

(1 row(s) affected)

Changed language setting to Français.
                               
------------------------------ 
10 juil 2008

(1 row(s) affected)

Changed language setting to polski.
                               
------------------------------ 
10 VII 2008

(1 row(s) affected)

Changed language setting to Suomi.
                               
------------------------------ 
10 heinä 2008

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources