• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 488
  • Last Modified:

Max(date)

the following command returns even the default '1900-01-01 00:00:00.000'. how can I avoid returning the default date?

isnull(convert(datetime, max(compDate),103),'') as 'Exp. comp. date'

thanks
0
ayha1999
Asked:
ayha1999
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
kamindaCommented:
You can use a case statement to check wether the result is equal to default '1900-01-01 00:00:00.000' if so return null or empty string.
isnull(convert(datetime, max(CASE WHEN DATEPART(year,compDate) = 1900 THEN NULL ELSE compDate),103),'')

Open in new window

0
 
Om PrakashCommented:
Try:

SELECT isnull(convert(datetime, max(compDate),103),'') as 'Exp. comp. date'
FROM table_name WHERE compDate <> '1900-01-01 00:00:00.000'
0
 
ayha1999Author Commented:
hi kaminda,
Incorrect syntax near the keyword 'convert'. with the statement.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
kamindaCommented:
There is a missing end statement sorry about that,

isnull(convert(datetime, max(CASE WHEN DATEPART(year,compDate) = 1900 THEN NULL ELSE compDate END),103),'')

Open in new window

0
 
ayha1999Author Commented:
still same error
 Incorrect syntax near the keyword 'convert'.
0
 
ralmadaCommented:
try

convert(varchar, max(compDate),103) as 'Exp. comp. date'

or

isnull(convert(varchar, max(compDate),103),'') as 'Exp. comp. date'
0
 
ayha1999Author Commented:
hi ralmda,
same error returned.
0
 
ralmadaCommented:
try

convert(varchar, max(compDate),103) as [Exp. comp. date]

If not, can you post you exact query?

finally can you please clarify if you're in SQL 2008?

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now