CONVERT Function - 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

SQL Server Development (2000)

CONVERT Function

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-09-20 : 15:10:22

When I pull date information out of a database table, it is formatted in the following way:

2005-09-21 00:00:00 (September 21st, 2005)

Is there a CONVERT function I can use to remove the time?

Thanks

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-09-20 : 15:12:06

Look up Cast and Convert in BOL.

Link:

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_ca-co_2f3o.htm

Nathan Skerl

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-09-20 : 15:22:45

Nothing seems to be working. The wierd thing about it is that in the database, there is only the date in the format I want. It is only when it comes out if the database that it adds on the time as all 0's.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-20 : 15:26:52

quote:
Originally posted by oahu9872

Nothing seems to be working. The wierd thing about it is that in the database, there is only the date in the format I want. It is only when it comes out if the database that it adds on the time as all 0's.

When it comes out of the database, are you referring to Query Analyzer or your application? Query Analyzer will display it as is if you aren't using CONVERT. Your application is probably adding the 0s. Show us the code.

Tara

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-09-20 : 15:29:04

I checked in Query Analyzer and it does add the 00's there. In the database table though it does not show them.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-20 : 16:12:42

What do you mean in the database it does not show them? What tool are you using to see this? Post the CREATE TABLE statement for this table and let us know which column it is.

Tara

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-21 : 00:59:35

>>Is there a CONVERT function I can use to remove the time?

If you use Front End Application to show the dates without time, use format(or equivalent function) there

Madhivanan

Failing to plan is Planning to fail

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-09-21 : 12:32:55

>>In the database table though it does not show them.

You mean when you right-click the table and select "Open Table" from Enterprise Manager? I think when you view datetime date through that method you see time truncated to the second. So, unless your time is >= '00:00:01:000' you will not see it in the "Open Table" console.

Performing a SELECT from QA would of course return the time unadulterated.

Tara, can you confirm/deny this?

Nathan Skerl

Kristen
Test

22859 Posts

Posted - 2005-09-21 : 15:16:44

I'm no Tara

, but:


SET NOCOUNT ON
SELECT CONVERT(datetime, '20051231 00:00:00.000')
SELECT CONVERT(datetime, '20051231 00:00:00.001')
SELECT CONVERT(datetime, '20051231 00:00:00.002')
SELECT CONVERT(datetime, '20051231 00:00:00.003')
SELECT CONVERT(datetime, '20051231 00:00:00.004')
SELECT CONVERT(datetime, '20051231 00:00:00.005')
--
SELECT CONVERT(datetime, '20051231 00:00:00.997')
SELECT CONVERT(datetime, '20051231 00:00:00.998')
SELECT CONVERT(datetime, '20051231 00:00:00.999')
SET NOCOUNT OFF

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-21 : 15:23:02

quote:

Originally posted by Kristen

I'm no Tara

, but:


SET NOCOUNT ON
SELECT CONVERT(datetime, '20051231 00:00:00.000')
SELECT CONVERT(datetime, '20051231 00:00:00.001')
SELECT CONVERT(datetime, '20051231 00:00:00.002')
SELECT CONVERT(datetime, '20051231 00:00:00.003')
SELECT CONVERT(datetime, '20051231 00:00:00.004')
SELECT CONVERT(datetime, '20051231 00:00:00.005')
--
SELECT CONVERT(datetime, '20051231 00:00:00.997')
SELECT CONVERT(datetime, '20051231 00:00:00.998')
SELECT CONVERT(datetime, '20051231 00:00:00.999')
SET NOCOUNT OFF

Kristen

I'm not sure I know what you mean.

Tara

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-09-21 : 16:28:35

Nah, what I meant was that Enterprise Manager (when viewing data in the Open Table Console) formats the datetime data rounded to the sec. Datetimes with 00:00:00:000 are truncated altogether.

Its just a formatting issue... like when it says <long text>, etc. I think this is what was confusing oahu9872:

quote:
Nothing seems to be working. The wierd thing about it is that in the database, there is only the date in the format I want. It is only when it comes out if the database that it adds on the time as all 0's.

I think he was equating Enterprise Manager with "in the database" and Query Analyzer with "out of the database."

Am I totally wrong here?

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources