displaying only the date in a DATETIME field in crystal reports

Question

displaying only the date in a DATETIME field in crystal reports

Juanita

SSCertifiable

Points: 5300
More actions
March 18, 2005 at 1:38 pm

#102444

HI. I have a developer that has some date fields that he wants to put on a crystal report. the fields are defined in sql with smalldatetime so of course the time is in the field also. He knows he can set an option in crystal reports to only display the DATE portion, but he wanted to know if there is something on the sql side that can be set to where ONLY the date will be stored in the sql table. i didn't see any other field type except DATETIME and SMALLDATETIME. any suggestions if this is possible?
Juanita

Viewing 4 posts - 1 through 3 (of 3 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

No, if you use DATETIME or SMALLDATETIME you always will have both: a date *and* a time portion. You can however set this time to midnight, so that it doesn't hurt anymore in queries. Here are several methods for this:

SELECT DATEADD(d,DATEDIFF(d,0,getdate()),0)

SELECT CAST(CAST(SUBSTRING(CAST(GETDATE() AS binary(8)),1,4) AS INT) AS DATETIME)

SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00000000 AS DATETIME)

SELECT CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112))

SELECT CONVERT(CHAR(8),GETDATE(),112)

SELECT CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)

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

If you aren't interested in the time anyway, consider updating the existing data and change the insertion process so that it will only deliver the date, not both.

Btw, the next version will have a DATE only datatype.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

Juanita,

Frank posted a lot of good examples. I had a similar question some time ago and I think I tested almost everything. The following works best for me in my case where we use several different report designers:

I create views that return data already joined, grouped, filtered etc so the report tool just select the appropriate fields. For dates I do the following:

Just for displaying, grouping or sorting for each day:

CONVERT(varchar(10), mydatefield, 111)

Same for month:

CONVERT(varchar(7), mydatefield, 111)

Sometimes I have to convert back to datetime as Frank says:

SELECT CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112))

that will still have time part but the time will be 00:00:000, means midnight, this is for comparison operators < and >. I may also use smalldatetime type for this conversion.

Yelena

Regards,Yelena Varsha

Juanita SSCertifiable Points: 5300 More actions · Answer 3

Thank you for the ideas !!! I will pass these along !!

Juanita