Blank a field if contains a date 1

djieon · Feb 1, 2012

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.

NiceArms · Feb 1, 2012

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!

djieon · Feb 1, 2012

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)

drlex · Feb 1, 2012

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é

gmmastros · Feb 1, 2012

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

djieon · Feb 1, 2012

Thats brilliant, thanks have a star!

gmmastros · Feb 1, 2012

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

djieon · Feb 1, 2012

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!

gmmastros · Feb 1, 2012

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

djieon · Feb 1, 2012

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]

gmmastros · Feb 1, 2012

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

djieon · Feb 1, 2012

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...

gmmastros · Feb 1, 2012

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

djieon · Feb 1, 2012

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....

gmmastros · Feb 1, 2012

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

djieon · Feb 1, 2012

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.

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!

Blank a field if contains a date 1

djieon

Technical User

NiceArms

Programmer

djieon

Technical User

drlex

Technical User

gmmastros

Programmer

djieon

Technical User

gmmastros

Programmer

djieon

Technical User

gmmastros

Programmer

djieon

Technical User

gmmastros

Programmer

djieon

Technical User

gmmastros

Programmer

djieon

Technical User

gmmastros

Programmer

djieon

Technical User

Similar threads

Part and Inventory Search

Sponsor