10 Character date field (I've tried cast & convert) ...

[Qmoto]

Thanks for taking a look at my question.

I'm sending data to corporate for analysis by their programmers, However, this past Friday I received the following email (emphasis supplied by me).

I've had a specific request to ask if you could change your date fields in your views to be date fields rather than string. It makes it much easier to extract specific date ranges when doing the loads. If you could do this for us we would appreciate it. Please make sure you provide only the date and not a date with time.

Here's the problem:
The code I'm using was nicely provided by gmmastros over on Link: this thread. (gmmastros: if you're reading this; the code is working beautifully, thanks again!)

,CASE
   WHEN MBF9.FEGHNB = '0' THEN '0'
   WHEN MBF9.FEGHNB <> '0' THEN CONVERT(VarChar(10), Convert(DateTime, Convert(VarChar(8), MBF9.FEGHNB + 19000000)), 101)
END AS INVOICE_DATE

The above code gives me the format they've requested (MM/DD/YYYY), but apparently their system treats the data I'm giving them as a string.

If I try and convert the varchar(10) back into a datetime field (lets use today's date: 01/15/07) I get 2007/01/15 00:00:00:000 as the result.




I guess the bottom line is this: Can I give corporate a DATETIME field that is only 10 chracters long in the form of MM/DD/YYYY?

 

[gmmastros]

You are using CASE so that when the field's value is 0, it returns a 0. Otherwise, it would return a string with a valid date. It sounds like they would prefer for you to return an actual date field (DateTime data type). In order for this to work, you'll need to do something about the 0's. Will they accept NULL for those records that are not actual dates?

Maybe something like...

[tt][blue]Convert(DateTime, Convert(VarChar(8), NullIf(MBF9.FEGHNB, 0) + 19000000)), 101)[/blue][/tt]

So... if the value is 0, NullIf will convert it to NULL. NULL Added to 19000000 will result in NULL. Then converted to varchar and DateTime will still be NULL.

I suggest you respond to the email asking them what value they would like returned when there is no date in the record. Suggest that NULL would probably be the most appropriate.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

I am pretty sure that datetime format will always include the time portion. Their programmers could possibly use this:

select cast('2007-01-15' as datetime)

To get the ten character string to get it back to datetime though?

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Hi George

Ignorance of certain subjects is a great part of wisdom

 

[Qmoto]

Thanks for the responses - sorry my reply is so slow!

gmmastros - I like that Idea, I think I'll implement the Nullif code. It will help shorten my current t-sql a bit and teach me a new trick at the same time! :0)

Alex - from what I've seen, you are correct, the datetime format always gives both date and time. Unfortunatly that's not what they want, but I do not believe I can give them what they are looking for.


Both: Please correct me if I'm wrong, but it seems to me that I have done all I can do and the rest is up to their programmers [!|?]

I'm giving them the requested MM/DD/YYYY format in varchar(10) format. It appears that they will need to convert that back to a datetime format on their end...

Thanks to you both!

 

[gmmastros]

From your quote...

I've had a specific request to ask if you could change your date fields in your views to be date fields rather than string. It makes it much easier to extract specific date ranges when doing the loads. If you could do this for us we would appreciate it. [!]Please make sure you provide only the date and not a date with time.[/!]

There are 2 ways that the 'red' part can be interpretted.

1. Give me the date only. No time information. In order to accomodate this request, you cannot supply the data as a DateTime field because there will always be a time component.

2. Give me the field in DateTime format. If there happens to be a time associated with the data, remove it so that it appears to have happened at midnight (hour, minute and seconds are all 0's).

If you take #2's interpretation, then the NullIf code should work pretty well, because it returns the field as DateTime. Since your original data does not have any time information, then your output won't either.

If you want to remove the time information from a DateTime field, but still preserve the date info, then....

Select DateAdd(Day, DateDiff(Day, 0, [!]GetDate()[/!]), 0) As FieldName

** Replace GetDate() with an actual field name.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Qmoto]

Unfortunately, they want option 1


So, I guess that confirms my suspicion that I now have to place the ball back in their court.

Thanks for the help!

 

[AlexCuse]

Are they bringing your output into a DateTime field? Because if they were, it should be adding the time component I think. Sounds to me like they are loading it into varchar (which of course, you will need to CAST or CONVERT in order to get it to sort like a proper date... unless it is sent as YYYYMMDD format, which of couse they don't want)

Good Luck!

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Qmoto]

I'm not sure how they are bringing the data in... that's part of the problem. We're going to schedule another meeting to talk with them about this and other issues.

Thanks again for all your help,
Steve