Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Blank a field if contains a date 1

Status
Not open for further replies.

djieon

Technical User
Aug 19, 2008
107
GB
Hi All, im new to SQL

I have a field which has a date called RM_JOB.ACTUAL_COMPLETION_DATE

in the database if a job is not completed it shows a date of 01/01/9999

What I want to be able to do is leave the date in the database alone but when I run a query if the field = 01/01/9999 then null else the actual completeion date.

Is this possible? If so is it easy to do?

thanks in advance

David.
 
do you mean,

Code:
SELECT CASE WHEN RM_JOB.ACTUAL_COMPLETION_DATE = '01/01/1999' THEN NULL ELSE RM_JOB.ACTUAL_COMPLETION_DATE END
FROM ATable

I feel sorry for people who don't drink, when they wake up in the morning that's as good as the're going to feel all day!
 
Hi, thanks for the quick reply, i have put that into my query but its not returning the value now... here is my sql, am i doing it right?

Thanks once again...

SELECT DISTINCT
[RM_INSTRUCTION].[INSTRUCTION_REFERENCE],
[RM_INSTRUCTION].[ITEM_UID],
[SECTION_ATTRIBUTES].[SECTION_NAME],
[RM_INSTRUCTION].[ITEM_IDENTITY_CODE],
[RM_INSTRUCTION].[ITEM_TYPE_UID],
[RM_INSTRUCTION].[INSTRUCTION_TIME],
[RM_INSTRUCTION].[ITEM_DETAIL_UID],
[RM_JOB].[CONTRACTOR_OFFICE_UID],
[RM_JOB].[REQUIRED_COMPLETION_DATE],
CASE WHEN RM_JOB.ACTUAL_COMPLETION_DATE = '01/01/1999' THEN NULL ELSE RM_JOB.ACTUAL_COMPLETION_DATE END
,
[CFG_MASTER_JOB_APPROVAL].[JOB_APPROVAL_STATUS_NAME]
FROM
( (((((RM_INSTRUCTION
LEFT OUTER JOIN
RM_INSTRUCTION_ITEM ON RM_INSTRUCTION.INSTRUCTION_UID = RM_INSTRUCTION_ITEM.INSTRUCTION_UID)
LEFT OUTER JOIN
RM_INSTRUCTION_STREET ON RM_INSTRUCTION.INSTRUCTION_UID = RM_INSTRUCTION_STREET.INSTRUCTION_UID)
INNER JOIN
SECTION_ATTRIBUTES ON RM_INSTRUCTION.SECTION_UID = SECTION_ATTRIBUTES.SECTION_UID)
INNER JOIN
ROAD ON ROAD.ROAD_UID = SECTION_ATTRIBUTES.ROAD_UID)
INNER JOIN
RM_JOB ON RM_INSTRUCTION.JOB_UID = RM_JOB.JOB_UID)
 
Is it a varchar (character) field or a true datetime one?
If the latter, you may need to amend the format to that to which your server has been configured (e.g. '1999-01-01')

soi là, soi carré
 
try this:

Code:
SELECT DISTINCT 
         [RM_INSTRUCTION].[INSTRUCTION_REFERENCE], 
         [RM_INSTRUCTION].[ITEM_UID], 
         [SECTION_ATTRIBUTES].[SECTION_NAME], 
         [RM_INSTRUCTION].[ITEM_IDENTITY_CODE], 
         [RM_INSTRUCTION].[ITEM_TYPE_UID], 
         [RM_INSTRUCTION].[INSTRUCTION_TIME], 
         [RM_INSTRUCTION].[ITEM_DETAIL_UID], 
         [RM_JOB].[CONTRACTOR_OFFICE_UID], 
         [RM_JOB].[REQUIRED_COMPLETION_DATE], 
         [!]NULLIF([/!]RM_JOB.ACTUAL_COMPLETION_DATE[!],'01/01/1999') As ACTUAL_COMPLETION_DATE[/!], 
         [CFG_MASTER_JOB_APPROVAL].[JOB_APPROVAL_STATUS_NAME]
FROM
        ( (((((RM_INSTRUCTION
LEFT OUTER JOIN
         RM_INSTRUCTION_ITEM ON RM_INSTRUCTION.INSTRUCTION_UID = RM_INSTRUCTION_ITEM.INSTRUCTION_UID)
LEFT OUTER JOIN
         RM_INSTRUCTION_STREET ON RM_INSTRUCTION.INSTRUCTION_UID = RM_INSTRUCTION_STREET.INSTRUCTION_UID)
INNER JOIN
         SECTION_ATTRIBUTES ON RM_INSTRUCTION.SECTION_UID = SECTION_ATTRIBUTES.SECTION_UID) 
INNER JOIN
         ROAD ON ROAD.ROAD_UID = SECTION_ATTRIBUTES.ROAD_UID)
INNER JOIN
         RM_JOB ON RM_INSTRUCTION.JOB_UID = RM_JOB.JOB_UID)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Thats brilliant, thanks have a star!
 
If the latter, you may need to amend the format to that to which your server has been configured (e.g. '1999-01-01')

drlex, this is not exactly fool-proof safe advice.

The problem here is ambiguity. When hard-coding dates, it's best to use a format that is not ambiguous.

Let's look at this date: January 2, 1999. This can be expressed in a lot of different ways. Ex: 1/2/1999, 2/1/1999, 1999-01-02, 1999-02-01, etc...

SQL Server has 2 date formats that are NOT ambiguous. The formats are:

yyyy-mm-dd[!]T[/!]hh:mm:ss.nnn
yyyymmdd hh:mm:ss.nnn

Here's the proof.

Code:
Set language German
Select Month('1999-01-02')

Set language english
Select Month('1999-01-02')

The 2 unambiguous date formats:

Code:
Set language German
Select Month('19990102')

Set language english
Select Month('19990102')

Set language German
Select Month('1999-01-02T00:00:00')

Set language english
Select Month('1999-01-02T00:00:00')


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
ok guys, final part of my question here...currently that field is a date time field, can I make it just a date field?

thanks once again!
 
That depends on the version of SQL Server you are using.

Microsoft added a new Date data type. I think this was added in SQL2008, but it may have been SQL 2008 R2. If you are using version of SQL Server that supports the Date data type, then you can simply change the data type.

You can (with all version of SQL Server), remove the time part of a DateTime data type like this:

Code:
Declare @Temp DateTime

Set @Temp = GetDate()

Select @Temp, 
       DateAdd(Day, DateDiff(Day, '20000101', @Temp), '20000101')



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
its failing on me, im trying to use cast?? not sure that is correct...he is what i have now, its near the bottom, i want the required completion date and the forula you did for me both to be just dates, they are currently date time

SELECT DISTINCT
[RM_INSTRUCTION].[INSTRUCTION_REFERENCE],
[RM_INSTRUCTION].[ITEM_UID],
[SECTION_ATTRIBUTES].[SECTION_NAME],
[RM_INSTRUCTION].[ITEM_IDENTITY_CODE],
[RM_INSTRUCTION].[ITEM_TYPE_UID],
[RM_INSTRUCTION].[INSTRUCTION_TIME],
[RM_INSTRUCTION].[ITEM_DETAIL_UID],
[RM_JOB].[CONTRACTOR_OFFICE_UID],
Cast ([RM_JOB].[REQUIRED_COMPLETION_DATE] as date),
NULLIF(RM_JOB.ACTUAL_COMPLETION_DATE,'01/01/9999') As ACTUAL_COMPLETION_DATE,
[CFG_MASTER_JOB_APPROVAL].[JOB_APPROVAL_STATUS_NAME]
 
What is the error message you are getting?

What do you get when you run this?

[tt]Select @@Version[/tt]



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
im not actually to query the sql server direct as im using another application to get to it and dont have access, so when I try that is does not work (invalid column name)
It is a fairly new system so would expect it to be the latest version of sql though...
 
Try the older method.

Code:
SELECT DISTINCT 
         [RM_INSTRUCTION].[INSTRUCTION_REFERENCE], 
         [RM_INSTRUCTION].[ITEM_UID], 
         [SECTION_ATTRIBUTES].[SECTION_NAME], 
         [RM_INSTRUCTION].[ITEM_IDENTITY_CODE], 
         [RM_INSTRUCTION].[ITEM_TYPE_UID], 
         [RM_INSTRUCTION].[INSTRUCTION_TIME], 
         [RM_INSTRUCTION].[ITEM_DETAIL_UID], 
         [RM_JOB].[CONTRACTOR_OFFICE_UID], 
         [!]DateAdd(Day, DateDiff(Day, 0, [/!][RM_JOB].[REQUIRED_COMPLETION_DATE][!]),0) As REQUIRED_COMPLETION_DATE[/!], 
         NULLIF(RM_JOB.ACTUAL_COMPLETION_DATE,'01/01/9999') As ACTUAL_COMPLETION_DATE, 
         [CFG_MASTER_JOB_APPROVAL].[JOB_APPROVAL_STATUS_NAME]

If this works, then it proves that you are using an older version of SQL Server.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Great, that works, but im struggling to get the line underneath to work as well, the one that contains the formula you did for me earlier....
 
Code:
SELECT DISTINCT 
         [RM_INSTRUCTION].[INSTRUCTION_REFERENCE], 
         [RM_INSTRUCTION].[ITEM_UID], 
         [SECTION_ATTRIBUTES].[SECTION_NAME], 
         [RM_INSTRUCTION].[ITEM_IDENTITY_CODE], 
         [RM_INSTRUCTION].[ITEM_TYPE_UID], 
         [RM_INSTRUCTION].[INSTRUCTION_TIME], 
         [RM_INSTRUCTION].[ITEM_DETAIL_UID], 
         [RM_JOB].[CONTRACTOR_OFFICE_UID], 
         DateAdd(Day, DateDiff(Day, 0, [RM_JOB].[REQUIRED_COMPLETION_DATE]),0) As REQUIRED_COMPLETION_DATE, 
         DateAdd(Day, DateDiff(Day, 0,NULLIF(RM_JOB.ACTUAL_COMPLETION_DATE,'01/01/9999')),0) As ACTUAL_COMPLETION_DATE, 
         [CFG_MASTER_JOB_APPROVAL].[JOB_APPROVAL_STATUS_NAME]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
brill, thats all working now, thanks so much for your help. I have stuck another question on this forum which I hope you can help with! :)

once again thanks.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top