Too much data in my cell

Question

Post reply

Too much data in my cell

shusta

Ten Centuries

Points: 1276
More actions
December 29, 2009 at 1:19 pm

#228770

Hello,
Two of the columns in my SQL query are returning more data than I need. The names of the columns are begin_date and end_date. From what I can tell I’m getting the date and time.
Each cell looks like this: “yyyy-mm-dd 00:00:00.000”
Can anyone tell me how I can return only the date (yyyy-mm-dd) for each cell? I’m trying to export this Access for other users to look at.
I’ve included my SQL code so you may see what I’m doing:
SELECT
asset.asset_tag,
contracts.contract_description,
contracts.contract_number,
item.description,
asset.serial_number,
asset.purchase_date,
contracts.num_licenses,
contracts.begin_date, (returns yyyy-mm-dd 00:00:00.000)
contracts.end_date, (returns yyyy-mm-dd 00:00:00.000)
asset.purchase_cost
FROM
asset INNER JOIN
item ON asset.item_id = item.item_id INNER JOIN
asset_contract ON asset.asset_id = asset_contract.asset_id INNER JOIN
contracts ON asset_contract.contract_id = contracts.contract_id
WHERE
asset.record_status = 1
AND contracts.record_status =1
and asset_contract.record_status = 1
And item.description Like '%item%'
order by asset_tag

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Just convert the DATETIME value to datatype DATE.

select

MyDate = convert(date,MyDateTime),

MyDateTime

from

( -- Test Data

Select MyDateTime = getdate()union all

select dateadd(hh,-11,getdate())union all

select dateadd(hh, 11,getdate())union all

select dateadd(hh, 12,getdate())

) a

Results:

MyDate MyDateTime

---------- -----------------------

2009-12-29 2009-12-29 15:38:09.973

2009-12-29 2009-12-29 04:38:09.973

2009-12-30 2009-12-30 02:38:09.973

2009-12-30 2009-12-30 03:38:09.973

kastnb01 SSC Journeyman Points: 89 More actions · Answer 2

Since you only want yyyy-mm-dd try convert

SELECT

asset.asset_tag,

contracts.contract_description,

contracts.contract_number,

item.description,

asset.serial_number,

asset.purchase_date,

contracts.num_licenses,

convert(char(10), contracts.begin_date, 120) as begin_date -- (returns yyyy-mm-dd)

convert(char(10), contracts.end_date, 120) as end_date -- (returns yyyy-mm-dd)

ss-457805 SSCertifiable Points: 5872 More actions · Answer 3

Personally i would use the DATE function which is a new thing in SQL 2008 and is much easier. If i was using 2005 then i would use convert.

blog: http://sarveshsingh.com Twitter: @sarveshsing

kastnb01 SSC Journeyman Points: 89 More actions · Answer 4

I agree with you about Date being clearer but since the OP didn't specify version I thought to give him an alternative.

Edit: Never mind - I just noticed the full name of this forum, please ignore all my blathering. (that's what I get for following links here instead of coming in the front door)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 5

Why won't Access take the native DateTime data type? Why are you trying to push data to Access instead of just using a "pass through" view or proc to get the data? Why aren't you formatting the data in the "presentation layer" of Access instead of trying to do it in T-SQL?

I don't really want answers to those questions... they're meant as food for thought for you. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andy Carroll SSC Enthusiast Points: 186 More actions · Answer 6

Hi,

I use the following and have found it really easy and reliable:

CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME)

Cheers

shusta Ten Centuries Points: 1276 More actions · Answer 7

I used SQL to create my queries and then copied the code to a new Access query (I'm using the Pass Through option/feature pointing back to the SQL server). The SQL server was giving me more than I wanted so I assumed Access would give me the same results but it didn't.