Date Conversion

Question

Date Conversion

Rixxe

Ten Centuries

Points: 1104
More actions
December 13, 2010 at 7:12 am

#244315

Hi all,
Recently with SQL server, i've been importing a large number of files with date fields...
Because the requirement of date fields change depending on the project, i constantly have to make adjustments... for example:
Changing the dates if they are missing..
ALTER function [dbo].[fixDate](@dateIn char(8))
returns char(8)
as
begin
declare @out char(8)
if substring(@dateIn, 7,2) = '00'
if substring(@dateIn, 5,2) = '00'
set @out = left(@dateIn,4) + '0701'
else
set @out = left(@dateIn,6) + '15'
else set @out = @dateIn
return @out
end
I noticed that when converting a text string '00000000' to date format, it doesnt work.
Every date field 'needs' to have a value...
Now in SAS (Large data handling program) you are able to just specify how the string is supposed to look (e.g. yyyy/mm/dd) and you can perform any function with that string.
Simple question would be: What String to Date limitations are there?
Is it best/possible to import to a specific Date format (103) ?
Is it possible to allow NULL/zero filled strings to be counted as dates, or allowed to be dates?
Obviously the subject of Formats (Dates mainly) is huge with any program, but any indepth answers would be greatly helpful.
Many thanks
J

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

As far as the conversion, you need a valid date, no matter what the format. If there is '00000', this isn't remotely valid.

What you can do is look for specific items like this and insert a specific date. What date would you use for '0000'? You could pick some reference date to use, or allow NULLs and use a NULL there.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

instead you should keep '1900-01-01' for '000000'

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Rixxe Ten Centuries Points: 1104 More actions · Answer 3

Steve Jones - SSC Editor (12/13/2010)
As far as the conversion, you need a valid date, no matter what the format. If there is '00000', this isn't remotely valid.
What you can do is look for specific items like this and insert a specific date. What date would you use for '0000'? You could pick some reference date to use, or allow NULLs and use a NULL there.

Thanks for your reply, however adding in a specific date like 1/1/1900 isn't really going to help.

If i set every '000000' date as default to 1/1/1900, when i search for the MINDATE() this will always be chosen, however because its technically null (In my books anyway) it shouldn't be included.

So replacing all '000000' dates with NULL, and allowing NULL is the best way?

Will i still be able to do date conversions with NULL fields? (I guess yes)

Thank you for the insight.

J

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

What do you mean by date conversions? NULL doesn't convert to anything but NULL.

NULL might be your best bet. You can always do MINDATE() ... WHERE mydate > '1/2/1900'

Rixxe Ten Centuries Points: 1104 More actions · Answer 5

Steve Jones - SSC Editor (12/14/2010)
What do you mean by date conversions? NULL doesn't convert to anything but NULL.
NULL might be your best bet. You can always do MINDATE() ... WHERE mydate > '1/2/1900'

When i mean date conversions: Converting to different date formats from a string.

None the less i like your idea of using Mindate, and greater than.

Many thanks

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

You can convert from strings to dates and vice versa (see Cast/Convert in BOL) with NULLs.