Stripping the time value from datetime

Question

Post reply

Stripping the time value from datetime

Rebecca Starr

Old Hand

Points: 311
More actions
July 22, 2004 at 3:40 am

#108885

Hi all, I am a beginner to this DB malarky, and so am very happy to have located this forum. I have read soooooooooooo many posts about stripping the time value from a datetime field. However, the majority of what I read refers to querying the data. However, I want to write a trigger that fires on the insert statement that will extract the time value and only store the date in teh database. Can anybody help me with this???? Also, I need to produce a report to display only the month and year part of the date like so: MARCH 2004. How do I do that? Thanks in advance.
bex

Viewing 11 posts - 1 through 10 (of 10 total)

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

kokyan SSCommitted Points: 1694 More actions · Answer 1

Dear Rebecca Starr,

Very easy!

Inside the instead of insert trigger, put this lines inside:

SET @DATE = CONVERT(VARCHAR(8), getdate(), 112)

Insert the date with value @DATE. You will get only the date without the time. Make sure the datatype of your column is varchar/char. If not, leading '00:00:00:000' will be added to your date. But, it is not advisable to use varchar/char to store the date (use datetime datatype).

When you query the results out from your database, you can use this select statement to format the result. For example,

SELECT datename(month, getdate()) + ' ' + cast(year(getdate()) as varchar(4))

Regards,
kokyan

Rebecca Starr Old Hand Points: 311 More actions · Answer 2

Thanks ever so much for your rapid reply Kokyan! I shall get on the case now! I can see I shall be a regular here!

Bex

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

This also works well. In practise I've found it a bit faster (though usually too little to notice)

Set @DATE = CAST(FLOOR(CAST (getdate() AS FLOAT)) AS DATETIME)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rebecca Starr Old Hand Points: 311 More actions · Answer 4

Thanks! As performance is an ongoing issue, any improvements, no matter how trivial, are always helpful!

Bex

kokyan SSCommitted Points: 1694 More actions · Answer 5

I am not quite agree with GilaMonster. With my method, I only convert once to varchar, but GilaMonster convert twice to FLOAT then to DATETIME back. This will create unnecessary burden to the server.

Regards,
kokyan

Rebecca Starr Old Hand Points: 311 More actions · Answer 6

Kokyan,

If I have converted the date to a varchar, can I still query the value with other date values?

For example, I have to extract the records from the tables on a monthly basis. Therefore, I need to compare whether the date is between a specific date value:

WHERE dbo.Cycle.EBill_Date>DATEADD(MM,-1,@Date)AND dbo.Cycle.EBill_Date<GETDATE()

If I have converted the date field to a varchar, can I still perform this comparison?

Bex

kokyan SSCommitted Points: 1694 More actions · Answer 7

Bex,

One question. Do you declare your column datatype as varchar or datetime? If datatype is datetime, then there are definately no problem! If the answer is NO, then you need to perform changes to the query to get the correct result.

WHERE CAST(dbo.Cycle.EBill_Date AS DATETIME) < GETDATE() .....[1]

At my previous post, I ask you to convert the date to varchar before insert into the table, but I also insist to use the datatype as datetime because I already expect this situation will occured. In case that you do not know, date in varchar format (yyyymmdd) can be inserted into datetime datatype column.

Avoid using SQL statement [1], because the indexes will be ignored and the performance will be degraded. Use datatype datetime and use back the original query:

WHERE dbo.Cycle.EBill_Date < GETDATE() .....[2]

Do you get what I mean above? Hope you can understand.

Regards,
kokyan

Rebecca Starr Old Hand Points: 311 More actions · Answer 8

I seeeeee. Yes, that makes sense. I was thinking that I would be changing the field value as opposed to the data value (the column is a datetime datatype, by the way). So the column still remains as a datetime datatype, and once I have changed the date value to varchar (to compose of the date only) I can insert this varchar instance into the datetime field, turning the value back to a datetime datatype. Is that correct?

Sorry if I sound ignorant, but I have only just started in this job, so even simple concepts are kinda confusing. But thanks for your help! I think I finally understand!

Bex

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Re performance, I'm just speaking from prior experience. I do MIS and as such run very large, very long queries. I found that changing the date rounding method from a conversion to varchar to a conversion to float round and convert back dropped one query from 7 min to 5 min.

Bear in mind that if you convert to varchar then insert into a db field that is datetime, SQL will do an implicit varchar to date time conversion for you. Also remember that numeric conversions are much faster than string conversions and dates are naturally stored as numbers

YMMV

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rebecca Starr Old Hand Points: 311 More actions · Answer 10

So in essence, I would be performing 2 conversions regardless of which method i use? And as you mentioned that dates are naturally stored as numbers, a number conversion will be performed quicker than a string conversion?

Bex