Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

want only date from date-time format Expand / Collapse
Author
Message
Posted Thursday, September 17, 2009 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 8:09 AM
Points: 6, Visits: 33
Hi All, I'm new to sql. Of late I created a table (in sql 2000) using
create table EMP
(
EMPNO int,
ENAME CHAR(10),
JOB CHAR(10),
MGR INT null,
HIREDATE DATETIME,
SAL INT,
COMM INT NULL,
DEPTNO INT
)

the table looked like ...
7369 SMITH HEADCLERK 7566 1980-12-17 00:00:00.000 800 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00.000 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00.000 2900 500 30

I want to get rid of that time part in the 'HireDate' column. I want only 1980-12-17 instead of 1980-12-17 00:00:00.000. please help me. Is declaring HIREDATE as a DATETIME incorrect? SQL 2000 perhaps does not accept DATE as data type, so I used DATETIME.
Also, if possible provide me a link from where I can study cast, convert & other similar utilities(sql 2000).
Thanking you in anticipation,
Post #789765
Posted Thursday, September 17, 2009 12:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 201, Visits: 389
It doesn't matter how it looks like in a table, but it is important how it looks like when you retrieve a data. So, you can convert it into different formats inside any of your SELECT statement like that:

SELECT CONVERT(CHAR(10), HIREDATE, 111) or
SELECT CONVERT(CHAR(10), HIREDATE, 105)

instead of just SELECT HIREDATE
Post #789899
Posted Friday, September 18, 2009 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 8:09 AM
Points: 6, Visits: 33
THANKS Sergey .. it worked....
Post #790359
Posted Monday, September 21, 2009 5:33 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:17 PM
Points: 1,588, Visits: 247
SQL Server 2008 has a Date datatype which will solve your problem when you are able to upgrade.

http://brittcluff.blogspot.com/
Post #791037
Posted Monday, September 21, 2009 8:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
Lynn has a nice page of common date routines at:
http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
Post #791173
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse